magic tables of triggers in sql server
the magic tables of a trigger are e开发者_开发百科mptied themselves or have to emptied explicitly? when we insert data into a table having a trigger on it, then first the data goes to the inserted magic table and then to the actual table. but after that does the inserted magic table retains its data or loses it?
From Using the inserted and deleted Tables
SQL Server automatically creates and manages these tables.
I presume you're talking about the INSERTED, DELETED records that exists in the context of the trigger? Why would you assume that they are anything but temporary as they cannot be accessed outside of the scope of the trigger.
I found the answer in a book as:
Once the trigger has completed, the data for that table is removed from the relevant logical tables.
The logical tables are held within TEMPDB temporary database, & therefore triggers will affect the performance of the tempdb & will be affected by any other process utilizing tempdb.
It is not possible to complete any further processing on these logical tables such as Creating and INDEX , as they are held in version store & the data can only be interrogated via a SELECT statement & cannot be modified.
We can only access these tables within a trigger to find out which records have been Inserted, Updated, or Deleted.
精彩评论