What system procedure is called when inserting table data?

Question

What system procedure is called when inserting table data?

Trent Peckham

SSC Journeyman

Points: 90
More actions
October 16, 2008 at 7:49 am

#194669

I have an interest in adding some functionality to all insert operations within a database and would prefer not to have to add insert triggers on every table. If I could embed the functionality into whatever procedure is called by the engine on data inserts that, in my mind, would be ideal. Can anyone tell me what system procedure that might be?

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

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

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 1

What you're considering isn't possible unless you have access to the source code of the SQL engine itself. There's no procedure that's called to insert rows into a table. Even if there was, it's highly likely that it would not be possible to modify it. Besides, modifying system objects of any form is a bad idea and tends to lead to serious problems.

If all your inserts are done via user-create stored procedures, you can change them. I don't know what you want to do, so I'm not sure what other options exist.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Trent Peckham SSC Journeyman Points: 90 More actions · Answer 2

Thanks for your response, Gail. My desire for doing this is to implement a primary key system (all surrogates) that enforces unique identifiers across the database (a.k.a. Enterprise Keys), rather than just at the table level. Our thoughts were that we may be able to modify a system procedure that is fired on each table insert to force the next identity to be selected from one central table rather than from each individual table. Otherwise, as you suggested, we will have to embed the code in each table's insert procedure.

Gail Shaw SSC Guru Points: 1004514 More actions · Answer 3

Have a look at uniqueidentifier with the newsequentialguid default. It may do what you want with no modifications at all.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Trent Peckham SSC Journeyman Points: 90 More actions · Answer 4

I've always tried to stay away from GUIDs because of the size and associated I/O hit. However, in this case I probably should have opened my mind to it because the hit may be worth it to gain the built-in functionality. I'll have to run some tests and see. Thanks very much, Gail.