Wednesday, March 28, 2012

Inserted and deleted temp tables ?

I want to know how inserted and deleted temp tables in SQL server work. My question is more regarding how they work when multiple users accessing the same database. Suppose two users update the database at the same time. In that case what are the values stored in the inserted and deleted tables.

I have a trigger that records changes to the database as in an audit trail. Like any other audit trail I insert data into my audit table from the inserted and deleted temp tables in MS SQL Server. I however am not clear as to how these inserted and deleted tables store values when two users update the database at the same time. Are there separate inserted and deleted tables for each session. The users access the database thru ASP pages.

The audit trail I am trying to use is http://www.nigelrivett.net/AuditTrailTrigger.html

I actually would like to store the inserted and deleted temp tables into other temporary tables so that I can access these tables thru a stored procedure. This is when the problem of same users updating the temporary tables is more pronounced.

Thanks in advance.If memory serves, the INSERTED and DELETED tables are special objects that belong to each session. So if two users update at the same time you should have two copies of these virtual tables

User1INSERTED/DELETED
User2INSERTED/DELETED

Brent

No comments:

Post a Comment