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

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

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

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Trigger on INSERT, UPDATE using INSERTED or Update
 Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Yak Posting Veteran

94 Posts

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

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

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

INSERT INTO YourAuditTable
SELECT *, GETDATE() AS datestamp

There are some really good examples on the MSDN page:
Go to Top of Page

Norsk Yak Master

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->
Go to Top of Page

Yak Posting Veteran

94 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  
 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.02 seconds. Powered By: Snitz Forums 2000