deleted table scope

Question

deleted table scope

Jim Herron

Grasshopper

Points: 22
More actions
March 11, 2005 at 10:03 pm

#66981

Does anyone know how to either pass a reference or memory copy of the deleted table from a trigger to a SPROC? I need access to the contents of the deleted table in a SPROC that is called by delete triggers. I can and have used a local temporary table however this is a fairly busy sproc and I would like to avoid the overhead of creating a local table when I already have deleted hanging around would like to just pass it down.
Thanks in advance for any suggestions
Jim

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

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

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 1

If you're referring to the virtual tables 'deleted' and 'inserted' that triggers have access to, those are only visible to the specific trigger. If you want to grab data from 'deleted', you must make the trigger do that for you. (most common is auditing, where the trigger inserts the data into a permanent audit table)

/Kenneth

david.roberts 43 Valued Member Points: 53 More actions · Answer 2

Reopening an old one here, but I have a similar problem and am wondering whether there is a solution.

I have a trigger which dynamically generates a script which refers to the (virtual) "inserted" and "deleted" tables. My plan was, having generated the script, to run it using EXEC (@Script), but it seems that the inserted and deleted tables are out of scope in the "EXEC" even though it is running within the trigger.

I can't pass the tables as parameters to the script, so is there any way of getting them in scope?

Thanks, David.

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 3

David

You could dump what you need from Inserted and Deleted into a temp table and see whether you can access it from there. Be very careful what you do in triggers, though - you could easily end up killing performance.

John

david.roberts 43 Valued Member Points: 53 More actions · Answer 4

Thanks for the prompt response, John. I'll give it a go. I'm very conscious of the performance issues, but thankfully in our situation the tables are updated infrequently, so a small degradation is acceptable.

david.roberts 43 Valued Member Points: 53 More actions · Answer 5

Thanks, that works just fine.

Obviously for the @Script to access the temporary tables, they have to have a fixed name. However, as I understand it, temporary tables have scope limited to the current connection, so presumably as long as none of my triggers are in any way recursive, I shouldn't get problems with the tables being overwritten?

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 6

Yes, I suppose that so long as your trigger doesn't cause itself to fire again, you should be OK. I wouldn't swear to that, though - you'll want to test it with all eventualities you can think of.

John