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
 General SQL Server Forums
 New to SQL Server Programming
 Trigger on INSERT, UPDATE using INSERTED or Update
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

olibara
Yak Posting Veteran

87 Posts

Posted - 11/14/2012 :  04:40:08  Show Profile  Reply with Quote
Hello

I need to create a trigger on Insert or Update
The goal is to create a log inserting a new recor in another table using the data of the updated or inserted record
Can I do this in one trigger ?
If yes how can I get the data from either updated or inserted record ?

Or do I have to create two disstinct trigger for update and insert ?

Thank for your help

sunitabeck
Flowing Fount of Yak Knowledge

5155 Posts

Posted - 11/14/2012 :  06:43:30  Show Profile  Reply with Quote
You can handle both update and insert in one trigger.

Within the trigger, you have access to two virtual tables - INSERTED and DELETED. INSERTED would contain all the new rows inserted (or rows as they are after update). DELETED would contain all the rows deleted (or rows as they were before the update).

So your trigger could conceivably be like this:
CREATE TRIGGER dbo.SomeTriggerName 
	ON dbo.YourTableName
FOR UPDATE, INSERT 
AS

INSERT INTO YourAuditTable
SELECT *, GETDATE() AS datestamp
FROM INSERTED;

GO
There are some really good examples on the MSDN page: http://msdn.microsoft.com/en-us/library/ms189799.aspx
Go to Top of Page

Lumbago
Norsk Yak Master

Norway
3271 Posts

Posted - 11/14/2012 :  06:48:06  Show Profile  Reply with Quote
There is no "updated" table...only "deleted" and "inserted". What happens is that when you do an update to a table, the old values (before the update) are put in the deleted table and the new values (after the update) are put in the inserted table. Both are only available inside the scope of the trigger.

Whether you should create one or two triggers really depends on the logic between the two operations...

- Lumbago
My blog-> http://thefirstsql.com
Go to Top of Page

olibara
Yak Posting Veteran

87 Posts

Posted - 11/14/2012 :  09:41:17  Show Profile  Reply with Quote
Thank a lot sunitabeck and lumbago this is more clear now !
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.03 seconds. Powered By: Snitz Forums 2000