Trigger on Inserted to insert new record

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 2008 Forums

Transact-SQL (2008)

Trigger on Inserted to insert new record

Author

Topic

flchico
Starting Member

46 Posts

Posted - 2010-09-08 : 17:14:41

Hi there,

I need to create an Insert Trigger to do the following:

Create an extra record with the same information except one field, which is not the Primary Key (Primary key is an identity key field). For simplicity if code = 600 then we need to add an extra record and the code in the new extra record will be 610, the rest of the information will be the same.

Ex:

pk name code
1 Mark 500
2 Tina 600
-- This triggers to create an extra record with code 610
3 Tina 610
4 Ray 400
5 Kay 600
-- This triggers to create an extra record with code 610
6 Kay 610

How can I do this in the insert trigger?

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 17:16:56

Use the inserted table like this:

INSERT INTO SomeTable (Column2, Column3, ...)
SELECT 610, Column2, Column3, ...
FROM inserted
WHERE code = 600

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

flchico
Starting Member

46 Posts

Posted - 2010-09-08 : 17:29:24

Thanks Tara,

and the fact that we are inserting code = 610 will avoid recursive inserts which would be TRAGIC! lol.

quote:
Originally posted by tkizer

Use the inserted table like this:

INSERT INTO SomeTable (Column2, Column3, ...)
SELECT 610, Column2, Column3, ...
FROM inserted
WHERE code = 600

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 17:32:36

The inserted table will only contain the data that is being inserted, so you won't get any recursive inserts. The example I posted does not look at your existing table, just adds additional row(s) to it.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

flchico
Starting Member

46 Posts

Posted - 2010-09-08 : 18:34:12

Wouldn't I get recursive inserts b/c we are inserting into the same table in the insert trigger? Well, that would only happen if we didn't change the code (leaving the code = 600) but since we change it to 610 for the newly inserted record when the trigger fires then it's gonna look for code = 600 so it stops. Maybe I didn't explain myself well.

Thanks again! Simple solution :).

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2010-09-08 : 18:57:48

My solution should do what you want.

The inserted trigger table will contains all rows to be inserted, regardless of the code. The query I posted will duplicate the 600 code ones but use 610 instead. So you'll get everything you inserted plus 610 rows if any 600 rows were found.

Give it a try and see if it solves your issues. If it doesn't, please post sample data and DDL for your table so that I can test.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources