Couple of questions regarding triggers 3

[pigsie]

Hi, hoping someone could help me with these (hopefully) easy questions (although I did look in the docs and couldn't find an answer).

1. Is there anyway inside a trigger to determine if it was an update or delete which triggered the trigger? I would like to use the same trigger for both actions *but* change a bit of the logic slightly if it is an update

2. Secondly, is there anyway to select multiple columns from the INSERTED table inside a trigger and assign them to variables instead of using multiple statements..e.g.:

set @myvar = (select a from inserted)
set @myvar2 = (select b from inserted)

etc.

Thanks in advance

 

[vongrunt]

1. Yup. Check for records in pseudotables inserted and deleted. For INSERT, inserted has records but deleted hasn't. For DELETE... ya know the rest.

2. select @myvar = a, @myvar2 = b from inserted. Btw. be aware that inserted/deleted may contain more than one record.

 

[mrdenny]

1. I don't know of a way to see if it was an update or delete other than looking to see if the deleted virtual table has any data in it. If it does it's a delete.

2. Yes, however doing this will cause the trigger to fail if more than one record is inserted/updated/deleted at one time.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pigsie]

Thanks for the response, but it seems I had an incorrect understanding of triggers - I thought the inserted/deleted tables only contained one row which contained the values of the row before it was updated/deleted that the trigger is firing on, are is this not correct?

 

[mrdenny]

The inserted/deleted tables contain all the rows that are effected.

If your update only effects one row then one row will be in the inserted table. If your update effects 100 rows then there will be 100 rows in the inserted table.

When writting triggers it is important to account for this, or your procedure will not work correctly when firred.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[pigsie]

Thanks - has this always been the case?

I thought that if an update/delete effects multiple rows then the trigger is fired *once* for each row but only the effected row would be in the inserted/deleted table..?

 

[ESquared]

Triggers are not fired once for each row. You must either use set-based queries to operate on all the rows at once (the best method) or a loop or cursor to step through the rows yourself (a less good method).

-------------------------------------
• Every joy is beyond all others. The fruit we are eating is always the best fruit of all.
• It is waking that understands sleep and not sleep that understands waking. There is an ignorance of evil that comes from being young: there is a darker ignorance that comes from doing it, as men by sleeping lose the k

 

[pigsie]

Guys

Thanks for the helpful replies, in light of this information - i'm going back to double check the triggers I have written in the past week to ensure that there are no problems as a result of the incorrect assumption I had of the inserted/deleted tables.

Thanks again