Posted by Erland Sommarskog on 01/11/08 22:12

Yas (yasar1@gmail.com) writes:
> I would like that when a record is deleted from Table 1, the trigger
> finds that record in Table2 and updates the date_removed filed with
> current time stamp.
> The primary key on both is combination of domain,admin_group and cn.
>
>
> CREATE TRIGGER [tr_updateTable2] ON [dbo].tbl_Table1
> FOR DELETE
> AS
> Update Table2
> SET date_removed = getDate()
>
> I'm stuck here, how do I manipulate on Table2 only the records that
> were deleted on Table1, so to only update date_removed filed for them
> in Table2?
> I guess i need to compare domain, cn and admin_group, but I don't know
> how.
>
>
> Any help would be greatly appreciated

Ed and Hugo gave you the code, but they did not really explain what
"deleted" is. In case you don't know it: in a trigger, you have access
to two virtual tables, "inserted" and "deleted". "inserted" holds the
after-image of the affected rows for an INSERT and UPDATE statement.
"deleted" holds a before-image of the affected rows for an UPDATE and
DELETE statement. "inserted" is empty with DELETE and "deleted" is
empty with INSERT.

Note that they are only visible directly in a trigger, and you cannot
access them from a stored procedure or dynamic SQL invoked by a trigger.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

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

[Back to original message]