Create Delete Trigger on Table1 to Update a filed on Table2

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! :-)

Yas wrote:

Quote:

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

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

Quote:

>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

Yas (yasar1@gmail.com) writes:

Quote:

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/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

Create Delete Trigger on Table1 to Update a filed on Table2

re: Create Delete Trigger on Table1 to Update a filed on Table2

re: Create Delete Trigger on Table1 to Update a filed on Table2

re: Create Delete Trigger on Table1 to Update a filed on Table2

Similar Microsoft SQL Server bytes

/bytes/development

/bytes/it

/bytes/about