deleted and inserted tables

[GerryGoldberg]

I am trying to write a delete trigger that is activated when multiple rows are deleted from one of my tables. I need to look at each of the deleted rows and I know that the resulting deleted table contains these rows but I don't know how to sequentially process them. Do I need to create a cursor from the deleted table and then use FETCH logic to retrieve each row, or can I process each row in the deleted table directly? I have the same question for an insert trigger and using the inserted table.

Thanks,

Gerry Goldberg

 

[Tim1]

Hi Gerry,

Within a trigger, you can refer to a table called deleted or a table called inserted exactly as you would do any other table.

They will have the same structure and field names as the table that they came from.

You can use cursors on them, or you can just use standard SELECT statements.

Tim

 

[GerryGoldberg]

Since I need to look at each row in the deleted table, and I don't think I can use FETCH (except with cursors), then I have to create a cursor from the the deleted table--right? Also, to find out if my deleted table has multiple rows, I don't I can simply check @@rowcount. Would I have to run a query like:

SELECT @nRows = COUNT (*) FROM deleted​

and then check the value of @nRows?

Thanks

 

[tlbroadbent]

Read the topic "Triggers: Multirow Considerations" in in SQL Books Online (BOL). It provides insight into working with multiple row update. Terry

http://members.home.net/tlbroadbent/prog.htm

------------------------------------
Experience is the hardest kind of teacher. It gives you the test first, and the lesson afterward.

 

[Tim1]

Hi Gerry,

That all sounds good to me. Instead of counting the rows, you could always declare your cursor, the check @@sqlstatus after the fetch statement. A value of 0 means that it has fetched a row, a value of 2 means that it has reached the end of the table.

Tim

 

[GerryGoldberg]

Thanks for the tips. I must have overlooked the multirow reference for triggers in Books on line. It helped a lot.

Gerry Goldberg

 

[TomSark]

And... @@rowcount WILL contain the count of the rows that were deleted when your trigger code begins. I usually set a variable to it in the beginning of the trigger before I have any other code so I don't have to query for the rowcount as stated above...

If at all possible, avoid coding cursors in a trigger... on the average, cursors are 7 times slower that normal SQL queries, and should be avoided in triggers. Again, if you can get around it...

Tom Davis
tdavis@sark.com

http://www.sarkdallas.com