SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Trigger for insert and update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

sqlpal2007
Posting Yak Master

184 Posts

Posted - 10/30/2013 :  10:09:23  Show Profile  Reply with Quote
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
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 10/30/2013 :  10:22:04  Show Profile  Reply with Quote
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

184 Posts

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

James K
Flowing Fount of Yak Knowledge

3660 Posts

Posted - 10/30/2013 :  10:35:20  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.12 seconds. Powered By: Snitz Forums 2000