Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Trigger for insert and update

Author  Topic 

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2013-10-30 : 10:09:23
Hello,

I have to write a trigger which inserts records in the table if data is updated. The problem is at one point my thrid party application updates the actual record (only one column updates) and then insert a new record. For example if I change the order status to cancel, the original row updates with one column and a new order is created with the cancel status.

For this kind of change I have to capture both the insert and update resulting addtion of two records with actions INSERT AND UPDATE in my audit table. How should I do this?

Can someone please help?

Thanks,
-P

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-30 : 10:22:04
If your trigger is for UPDATE and INSERT, then the updated row and the inserted row should be captured. A trigger can be written for INSERT, UPDATE and/or DELETE.

If you need more help, can you post your existing trigger code?
Go to Top of Page

sqlpal2007
Posting Yak Master

200 Posts

Posted - 2013-10-30 : 10:24:36
I have three triggers. UPDATE, DELETE and DELETE. Shoudl I combine INSERT and UDPATE into one trigger?
Go to Top of Page

James K
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-10-30 : 10:35:20
You can combine the triggers into one if you like, but not required. (I assume you meant 3 triggers for "UPDATE, INSERT and DELETE" rather than "UPDATE, DELETE and DELTE").

What you should take care though, is to handle the case where more than one row may be updated/inserted/deleted in a single DML statement. In such cases, the trigger will be called only once. The INSERTED and DELETED virtual tables will have more than one row in that case.
Go to Top of Page
   

- Advertisement -