question

Damus avatar image
Damus asked

Trigger in sql server 2005

How to create a trigger in sql server 2005 to insert data into another table while inserting into the first table,please provide me some syntax regarding to insert statement like

"insert into emp values(:old.eno,:old.ename,:old.sal)"

in sql server what we can use for the above statement.

trigger
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image
Matt Whitfield answered

Ok, it works like this. There are two virtual tables available in triggers, inserted and deleted.

These tables both have exactly the same schema as the table upon which the trigger operates. So, let's use a table as an example:

CREATE TABLE [dbo].[myTest] (
  [ID] [int] IDENTITY (1,1) NOT NULL PRIMARY KEY CLUSTERED,
  [Value1] [int] NOT NULL,
  [Value2] [int] NOT NULL)

So, to insert the inserted rows into another table too, you would use an AFTER INSERT trigger containing the following code:

INSERT INTO myOtherTable ([ID], [Value1], [Value2])
SELECT [ID], [Value1], [Value2] FROM inserted

Note: The above code assumes that myOtherTable does not have ID set to the IDENTITY for the table, otherwise you would need to surround that code with SET IDENTITY_INSERT ON/OFF

For extra info:

In an UPDATE trigger, you have both virtual tables, deleted and inserted. This time, deleted contains the rows as they were before the update, and inserted contains the same rows as they were after the update.

In a DELETE trigger, you only have the deleted virtual table, which contains the rows which have been deleted.

Some extra tips:

If you want to create a trigger that replaces an operation for a particular table, use an INSTEAD OF trigger. In this way, you can do things like create views which can accept INSERT, UPDATE and DELETE operations. This can be useful when you're trying to integrate a third party database schema tightly with your own. A recent example of this for me was integrating a forum component into our website - we used views with INSTEAD OF triggers to allow the forum component to work with what it believed to be it's own tables, when actually they were SYNONYMS to views with INSTEAD OF triggers in our schema.

A golden rule:

Always remember that a trigger can operate on more than one row. You can say to yourself 'I know this table will only be updated one row at a time' - but you will be wrong. One day it will need to be bulk updated for some reason, and it will be you that will have to sort it out.

10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Rob Farley avatar image
Rob Farley answered

You want to select from the internal table called 'deleted'

insert emp (eno, ename, sal)
select eno, ename, sal
from deleted;
2 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Matt Whitfield avatar image Matt Whitfield ♦♦ commented ·
Are you sure you meant deleted? :)
0 Likes 0 ·
Rob Farley avatar image Rob Farley commented ·
Yeah - the question used :old
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.