help trigger

Question

Post reply

help trigger

MC-142370

Old Hand

Points: 390

October 4, 2004 at 7:48 am

#89450

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????

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 1

I thought I just replied to this, but now don't see the response. I apoligize if it shows up twice.

You should not use the "Instead of" trigger in this case. With the "after" type of trigger, you don't delete the row from the original table. Also, I don't think I would waste time checking the audit table for an existing row.

Try the following:

Create TRIGGER TrigDelete

ON dbo.TOrig

For Delete

AS

Insert into TAudit

select * , 3, GetDate(), System_USer

from Deleted

GO

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

MC-142370 Old Hand Points: 390 More actions · Answer 2

I would've liked to use For Delete trigger but i can't since there are text columns in the table. 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?

Kathi Kellenberger SSChampion Points: 11811 More actions · Answer 3

You are correct that there are problems if you are using text columns, but you can pull some information from deleted. If you are willing to have partial information from deletes, then you can do something like this:

Create TRIGGER TrigDelete

ON dbo.TOrig

For Delete

AS

Insert into TAudit(Liq_ID, action, recordedDate, user)

select Liq_ID , 3, GetDate(), System_USer

from Deleted

GO

Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor

MC-142370 Old Hand Points: 390 More actions · Answer 4

And if i need all data from deleted, then what???