after delete trigger

[sparkbyte]

Does enyone have an example of an after delete trigger that copies the deleted row(s) into an archive table and adds who deleted it and when??

I have an activity table (schema.Activity) and the archive table (Archive.Activity).


Thanks

John Fuhrman

 

[trevil620]

You need to fire the trigger BEFORE the delete: Check this:

http://stackoverflow.com/questions/...o-a-table-before-delete-with-a-delete-trigger

The speculative judgment of the quality of an answer is based directly on … what was the question again?

 

[SaltyTheFrog]

Here is one we use.. I'm not dure about the before and after stuff.

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[tr_Object_d] ON [dbo].[OBJECT] 
FOR DELETE
AS


IF @@ROWCOUNT =0
RETURN

insert objecthist(OBJECT_ID,OBJECT_CLASS_ID,NAME,SERVER,RELATIVE_PATH,IS_LOCKED,LOCKED_BY,
		HAS_INDEXES,IS_IMAGE,IS_CHARACTER,IS_FULL_TEXT_INDEXED,BATCH_NAME,CREATE_TIME,Indexed_By,Scanned_By,
		DateChanged,ChangedBy,Change,INDEXED_TIME,fkImageFax,OverrideTasking)

select OBJECT_ID,OBJECT_CLASS_ID,NAME,SERVER,RELATIVE_PATH,IS_LOCKED,LOCKED_BY,HAS_INDEXES,IS_IMAGE,
	IS_CHARACTER,IS_FULL_TEXT_INDEXED,BATCH_NAME,CREATE_TIME,Indexed_By,Scanned_By ,getdate(),user_name(),'D',INDEXED_TIME,fkImageFax,OverrideTasking
from deleted 


GO

 

[gk53]

the same code only after delete will work

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE TRIGGER [dbo].[tr_Object_d] ON [dbo].[OBJECT] 
AFTER DELETE
AS


IF @@ROWCOUNT =0
RETURN

insert objecthist(OBJECT_ID,OBJECT_CLASS_ID,NAME,SERVER,RELATIVE_PATH,IS_LOCKED,LOCKED_BY,
		HAS_INDEXES,IS_IMAGE,IS_CHARACTER,IS_FULL_TEXT_INDEXED,BATCH_NAME,CREATE_TIME,Indexed_By,Scanned_By,
		DateChanged,ChangedBy,Change,INDEXED_TIME,fkImageFax,OverrideTasking)

select OBJECT_ID,OBJECT_CLASS_ID,NAME,SERVER,RELATIVE_PATH,IS_LOCKED,LOCKED_BY,HAS_INDEXES,IS_IMAGE,
	IS_CHARACTER,IS_FULL_TEXT_INDEXED,BATCH_NAME,CREATE_TIME,Indexed_By,Scanned_By ,getdate(),user_name(),'D',INDEXED_TIME,fkImageFax,OverrideTasking
from deleted 


GO

 

[sparkbyte]

Thanks,

altering it to a FOR Delete seems to have got it.

Also, thanks for the reminder to test for empty set!!

Thanks

John Fuhrman