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

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

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

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

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

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

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

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

ad the condition of the deleted deleted record's identity ID to the
update statement. the ID can be fetched using

select @recKeyID = {identity column name} from deleted

the @recKeyID is the variable you need to create.

SagiPhoenix@AlbionLab


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

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

SagiPhoenix@AlbionLab (zkvneml@gmail.com) writes:
> ad the condition of the deleted deleted record's identity ID to the
> update statement. the ID can be fetched using
>
> select @recKeyID = {identity column name} from deleted

No! No! No!

A trigger fires once per *statement* and there can be more than one
row in deleted/inserted.

And who says that the table has an IDENTITY column? If you have IDENTITY
columns in all your tables, you have a habit that you need to change.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx