Before Trigger

Question

Before Trigger

dankwart menor

Ten Centuries

Points: 1130
More actions
October 29, 2008 at 6:19 am

#195149

Hey guys, here's the problem:
I initially wanted to implement a trigger that fires BEFORE the execution of an INSERT statement. As there is only FOR|AFTER|INSTEAD of but no BEFORE option (why?), I must go another way.
My goal is to set a value (lets call it is_valid) of the rows with the same ID as the inserted row. But not the is_valid of the inserted row.
create trigger validTrigger on xy FOR insert as
update xy set is_valid = 0 where ID=(select ID from INSERTED)
GO
If I do it like this, the inserted row is updated too. So, how can I update all the other rows (or even better: only the one with the latest timestamp) but not the inserted one?
Any suggestions?
Thank you,
Dankwart
--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Jack Corbett SSC Guru Points: 184394 More actions · Answer 1

When you just use FOR you are actually creating an AFTER trigger. What you want is an INSTEAD OF trigger. The issue with INSTEAD OF triggers is that you need to redo your action as part of the trigger. So the trigger you'd be looking for would be something like this:

[font="Courier New"]CREATE TRIGGER validTrigger ON xy

Instead OF INSERT

AS

BEGIN

SET NOCOUNT ON

UPDATE xy

SET is_valid = 0

WHERE

/* note I am using in as you will get an error

if you use = and it is a batch update */

ID IN (SELECT ID FROM INSERTED)

-- redo the insert - this won't refire the trigger

/* you'd have to use a column list here if

you have an identity column and/or use

set identity_insert on and off to keep the

same identity value*/

INSERT INTO xy

SELECT

*

FROM

inserted

END[/font]

I also suggest that you read this article, http://www.sqlservercentral.com/articles/Triggers/64214/

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dankwart menor Ten Centuries Points: 1130 More actions · Answer 2

Awesome! Thanks a lot Jack.

I was dazzled by the absence of a BEFORE Trigger. But well, thats how it works.

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

dankwart menor Ten Centuries Points: 1130 More actions · Answer 3

Also thanks for the

ID IN (SELECT ID FROM INSERTED)

I actually did it like this, the example was just quick and dirty....

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]

Jack Corbett SSC Guru Points: 184394 More actions · Answer 4

Glad I could be of help. As for recommending the "IN", I'm glad you were using it in the real trigger, most of the time people who post about triggers don't understand that they work on sets not individual rows so I always try to get them to that.

Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question

dankwart menor Ten Centuries Points: 1130 More actions · Answer 5

Still I'll have a look at that article you suggested...

One can always learn something new.

--------------------------------------------------------
[font="Comic Sans MS"]Correct me if I'm wrong[/font]