|
|
Posted by Hugo Kornelis on 01/11/08 17:50
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
Navigation:
[Reply to this message]
|