CODEWITHSUNDEEP
sqlsql-server
user568171
user568171

Reputation:

Help Understanding the inserted and deleted Table SQL Server

If a run an INSERT INTO statement that triggers an INSERT AFTER trigger. What table/row(s) does the inserted and deleted objects represent in the trigger? For example,

INSERT INTO Person.person (personID, name) VALUES (1, 'robert')

CREATE TRIGGER myTrigger  
ON Person.person  
AFTER INSERT AS  
BEGIN  
 insert into Person.PersonMovies (personID, movieID)  
 select inserted.personID  from inserted   _--i know the code is incomplete, but i'm curious only about the inserted part._  
END

I'm confused as to what records are held by the inserted table. Would it be the records in the insert statement that triggered the trigger or the Person.PersonMovies table?

Upvotes: 1

Views: 4637

Answers (4)

Doug Chamberlain
Doug Chamberlain

Reputation: 11351

The rows in the pseudotables inserted and deleted are based on the table defined in you ON clause.

Upvotes: 1

Dog Ears
Dog Ears

Reputation: 10025

Yes, the inserted/deleted tables gives your trigger code access to the records changed by the statement that caused (triggered) the trigger. Also remember that your statements could have effected multiple rows, so your deleted or inserted tables could have multiple records.

Upvotes: 2

KeithS
KeithS

Reputation: 71573

The inserted and deleted pseudotables hold one record for each row in the table that was created or deleted by the statement that fired the trigger. For an update, the deleted table holds the old version of the records, and the inserted table holds the new. The schema of the pseudotables matches the actual table.

Upvotes: 0

Joe Stefanelli
Joe Stefanelli

Reputation: 135848

The Inserted table holds the rows from the original Insert statement that caused the trigger to fire.

See also this MSDN article.

Upvotes: 0

Related Questions