By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,156 Members | 962 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,156 IT Pros & Developers. It's quick & easy.

Help Trigger

P: n/a
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
Share this Question
Share on Google+
4 Replies


P: n/a
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

P: n/a
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

P: n/a
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

P: n/a
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.