Re: SQL Server 2005 Triggers
Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance
Mark Shields (MarkShields@xxxxxxxxxxxxxxxxxxxxxxxxx) writes:
IS THERE A KEYWORD IN THE TRIGGER THAT REFERS TO THE NEWLY INSERTED
RECORD?
No, but in a trigger you have access to two virtual tables "inserted"
and "deleted". "inserted" holds the inserted rows (note plural) and
in case of an UPDATE trigger the after-image of the updated rows.
"deleted" holds the rows deleted by a DELETE statement and a before-image
of update rows in an UPDATE trigger.
The two tables have the same columns as the parent table of the trigger.
So the correct way of working is to join with inserted, just like in the
example that Aaron showed you.
--
Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx
.
Relevant Pages
- RE: Trigger to populate archive database table with text datatype
... > In a DELETE, INSERT, or UPDATE trigger, SQL Server does ... > not allow text, ntext, or image column references in the ... > If the compatibility level is 80 or higher, SQL Server ...
(microsoft.public.sqlserver.server) - Re: Identifying rows in update trigger when primary key has been updated
... This is equiv to... ... "Ellis Horowitz" wrote in message ... > between the inserted and deleted tables within an update trigger when ... > field) SQL Server link the inserted and deleted tables? ...
(microsoft.public.sqlserver.programming) - Re: Identifying rows in update trigger when primary key has been updated
... but I have never seen this (I also rarely let primary key ... Pro SQL Server 2000 Database Design ... >> between the inserted and deleted tables within an update trigger when ...
(microsoft.public.sqlserver.programming) - Re: Mass Update on Table with Trigger
... an update trigger (which updates the field whenever a ... As a result I'm getting an error: "Subquery ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ... Books Online for SQL Server 2005 at ...
(comp.databases.ms-sqlserver) - Re: text fields from inserted, deleted tables
... In a DELETE, INSERT, or UPDATE trigger, SQL Server does not allow text, ... The text, ntext, and image values in the ... values are returned for inserted or deleted text, ntext, or image columns ...
(microsoft.public.sqlserver.programming) |
|
