469,593 Members | 1,731 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,593 developers. It's quick & easy.

Help Trigger

i want to audit transactions done to table TOrig.
I created table TAudit same as TOrig in addition to ActionID (1 for
insert, 2 for update, 3 for delete), System Date and System User.
I created triggers on TOrigto insert into TAudit in case of insert,
update & delete.

TOrig contains text column. So i created instead of Trigger like:

Create TRIGGER TrigDelete
ON dbo.TOrig
Instead of Delete
AS
declare @id int
select @id=Liq_ID from Deleted

If Not Exists (Select * from TAudit where Liq_ID=@id and ActionID=3 )
BEGIN
Insert into TAudit
select * , 3, GetDate(), System_USer
from Deleted

Delete from TOrig where Liq_ID=@id
END
GO

PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO????
Jul 20 '05 #1
4 1407
On 4 Oct 2004 06:21:50 -0700, Marie-Christine wrote:
PROBLEM: is that Torig is a detail table to a master table and has a
cascade delete relationship with the master table.
So Instead Trigger does it work. What do I DO????


Hi Marie-Christine,

1. Create the trigger as an AFTER trigger, not as an INSTEAD OF trigger.

2. Rewrite the trigger, so that it will handle multi-rows inserts, updates
and deletes as well.

Best, Hugo
--

(Remove _NO_ and _SPAM_ to get my e-mail address)
Jul 20 '05 #2
MC


I would've liked to use For Delete trigger but i can't since there are
text columns in the table. Neither can i use After Trigger. And i can't
retrieve rows from Deleted table when there are text columns. That's why
i used Instead of Delte trigger. But now i'm hung cause Instead of
Delete trigger doesn't work with Cascade. That's why i'm confused now.
There are 2 problems: text columns in the table, and cascade delete. How
do i solve the 2 problems?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3
MC


ANyone!!! no solution?????

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #4
MC <an*******@discussions.microsoft.com> wrote in message news:<41**********************@news.newsgroups.ws> ...
ANyone!!! no solution?????

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!


If you can't use a trigger, you could put the DELETE and the audit
logic into a procedure, then make users and applications use the
procedure for all deletions.

Simon
Jul 20 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by Curtis Gilchrist | last post: by
9 posts views Thread by Martin | last post: by
11 posts views Thread by Jules Alberts | last post: by
reply views Thread by Michael L | last post: by
15 posts views Thread by Jay | last post: by
3 posts views Thread by Sam Durai | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.