Create Delete Trigger on Table1 to Update a filed on Table2

[Yas]

Hi everyone

I am trying to create a DELETE Trigger. I have 2 tables. Table1 and
Table2. Table 2 has all the same fields and records as Table1 + 1
extra column "date_removed"

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

Thanks! :-)

[Ed Murphy]

Yas wrote:

I am trying to create a DELETE Trigger. I have 2 tables. Table1 and
Table2. Table 2 has all the same fields and records as Table1 + 1
extra column "date_removed"

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.

update Table2
set date_removed = GetDate()
from Table2 t
join deleted d on t.domain = d.domain
and t.cn = d.cn
and t.admin_group = d.admin_group

[Hugo Kornelis]

On Fri, 11 Jan 2008 06:49:02 -0800 (PST), Yas wrote:

>Hi everyone

I am trying to create a DELETE Trigger. I have 2 tables. Table1 and
Table2. Table 2 has all the same fields and records as Table1 + 1
extra column "date_removed"

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

Thanks! :-)

Hi Yas,

Here's an alternative, using a more portable syntax for the UPDATE
statement, that also has less "issues" (but don't worry - none of these
issues affect you when joining on the primary key, as is the case here;
I just wanted to provide this alternative for completeness' sake).

UPDATE Table2
SET DateRemoved = CURRENT_TIMESTAMP
WHERE EXISTS
(SELECT *
FROM deleted AS d
WHERE d.domain = Table2.domain
AND d.cn = Table2.cn
AND d.admin_group = Table2.admin_group);

--
Hugo Kornelis, SQL Server MVP
My SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis

[Erland Sommarskog]

Yas (ya****@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx