Help trigger

• MC-142370

  Old Hand

  Points: 390

  More actions

  October 4, 2004 at 7:46 am

  #86676

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

• Kathi Kellenberger

  SSChampion

  Points: 11811

  More actions

  October 4, 2004 at 3:36 pm

  #525297

  I'm not sure why you are using the "instead of" type of trigger.  With the "after" type of trigger you don't need to delete the row.  Also, I don't think I would check to see if a row exists in the audit table either.  Another, thing, you also have to have rights on the audit table.

  I would rewrite your trigger like this:

  Create TRIGGER TrigDelete

  ON dbo.TOrig

  For Delete

  AS

   

    Insert into dbp.TAudit

    select * , 3, GetDate(), System_USer

    from Deleted 

   

   GO

  Hope this helps,

  Kathi Kellenberger

   

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