INSTEAD OF DELETE trigger trouble

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

INSTEAD OF DELETE trigger trouble

Author

Topic

cupeet987
Starting Member

11 Posts

Posted - 2008-01-08 : 11:01:48

I'd like to use an INSTEAD OF DELETE trigger to, instead of deleting a row, just mark a bit in each row. I've been doing this the following way:

INSTEAD OF DELETE
BEGIN
UPDATE table
SET column = 1
WHERE column = SELECT column FROM DELETED.

But this seems rather inneficient and doesn't work with multi-row deletes.

Someone can help me doing this in the most ellegant way?

Thank you very much for your precious time.

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-08 : 11:08:47

try this:

UPDATE yt SET
column = 1
from <yourTable> yt
join deleted d on d.<yt.primaryKey> = yt.<yt.primarykey>

Be One with the Optimizer
TG

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2008-01-08 : 11:16:13

[code]UPDATE t
SET column = 1
FROM TABLE t INNER JOIN deleted d
ON t.column = d.column[/code]

KH
[spoiler]Time is always against us[/spoiler]

TG
Master Smack Fu Yak Hacker

6065 Posts

Posted - 2008-01-08 : 11:24:01

I don't think you want to join on the [deleted] bit column. You need to use what ever the primary key (or an alternate/logical key) from [TABLE]

Be One with the Optimizer
TG

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources