Trigger - Insert,Update,Delete help

[jgurgen]

how would i write a trigger for insert,update and delete for LoanID,HistoryCounter,TranDate,TranCode,Desc,TranAmt from service.History to ifs.History? I think my insert is correct but im not sure how i would do the update and delete also.

i have this right now...

create trigger ifs_historycopy on History For insert,update,delete as
begin
DECLARE @ID varchar(10), @HistoryCounter smallint, @TranDate datetime, @TranCode smallint, @TranAmt money
SELECT @ID = (SELECT Right('0000000000' + LoanId, 10) FROM Inserted)
SELECT @HistoryCounter = (Select HistoryCounter FROM Inserted)
SELECT @TranDate = (Select TransactionDate FROM Inserted)
SELECT @TranCode = (Select TransactionCode FROM Inserted)
SELECT @TranAmt = (Select TransactionAmt FROM Inserted)
insert into ifs.dbo.history (loanid,HistoryCounter,TranDate,TranCode,TranAmt) values (@ID,@HistoryCounter,@TranDate,@TranCode,@TranAmt)
end;

 

[jbenson001]

You would need to replicate the code for deleted records and use:
SELECT @ID = (SELECT Right('0000000000' + LoanId, 10) FROM Deleted)
...
...
etc.

I would do a count of the deleted table first to see if you actually need to run that part of the code.

Jim

 

[jgurgen]

i am assuming the same thing with FROM UPDATED too?

 

[jbenson001]

There is no Updated psudeo table. Since mutiple updates can occur, the trigger only fires once for each batch of updates, not for EACH update. You may want to create a separate UPDATE trigger, then join to the inserted table to get your values and insert them. There have been multiple examples posted on how to do update triggers.

 

[SQLSister]

YOur trigger will fail if more than one recrod is updated or deleted or inserted. Never write a trigger to rpocess only one record at a time. You need to do this in sets of data. Instead of pulling to variables, join to the inserted or deleted pseudotables. For updated, if you want the old values pull from the deleted table if you want the new values pull from the inserted table.

Questions about posting. See faq183-874
Click here to help with Hurricane Relief