Delete trigger

[ehx6]

hi, I need your help .
I need to log every row that is deleted from a transaction_table. so I created a trigger on the table like this:
CREATE TRIGGER TranLog_d ON [dbo].[Transactions]
FOR DELETE
AS
declare @id int , @Tran varchar(10) , @Amount numeric(18,2)
select @id = id , @Tran = Tran, @Amount = amount
from deleted

insert Tran_deleted (id, Tran, Amount) values (@id , @Tran, @Amount)

The above trigger works fine when I am deleting single row at a time, but when I delete more than one record,lets say 3 records, the last deleted record is appended to the Tran_deleted table.
Any advice how to capture all deleted rows using trigger
Thanks
Ehx

 

[Catadmin]

I believe something like the following should work:

Create Trigger TranLog_D on dbo.Transactions
for delete
As insert Tran_Deleted (fieldlist)
(Select * from deleted)



Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!

 

[Catadmin]

Sorry, let me clarify using your variables.

Create Trigger TranLog_D on dbo.Transactions
for delete 
As insert Tran_Deleted (ID, Tran, Amount)
(Select * from deleted)

I did test it using the pubs example DB and it inserted 3 rows into my secondary table when I ran the following statement:

Delete from sales
where ord_num like 'P2%'

Of course, the table you are putting the values into has to be designed exactly like the table you are deleting the values from. Otherwise, your trigger won't work correctly.

You can also use the same code to track insertions by changing the select statement to read "Select * from inserted" on a For Insert trigger.




Catadmin - MCDBA, MCSA
Remember, "Work" is a four letter word. And you know what your mother told you about using four letter words!