INF: Using Temporary Tables with Stored Procedures/Triggers

ID: Q65756

The information in this article applies to:

SUMMARY

A stored procedure can reference a temporary table with no problems, as long as that table exists. For example:

  1. Temporary table T1 is created.


  2. Stored procedure PROC1 is created (it accesses T1 for data).


  3. PROC1 is executed (works properly).


  4. Temporary table T1 is dropped.


  5. Temporary table T1 is re-created.


  6. PROC1 is executed (works properly).


However, a trigger can only access the temporary table if the table existed prior to the creation of the trigger. For example:
  1. Temporary table T1 is created.


  2. Trigger TRIG1 is created (it accesses T1 for data).


  3. TRIG1 is executed (works correctly).


  4. Temporary table T1 is dropped.


  5. Temporary table T1 is re-created.


  6. TRIG1 is executed (DOES NOT WORK).



MORE INFORMATION

There are several factors involved that cause this behavior to occur in this manner. The first factor involved is the naming convention of temporary tables. Each time a temporary table is dropped and re-created, the new table name will be slightly different. This is transparent to the user, but not to the stored procedures and triggers.

The second factor involved is a difference in stored procedures and triggers. Basically, triggers are compiled only once, while stored procedures can be recompiled when needed. Therefore, the stored procedure realizes that the temporary table is different and will recompile. The trigger does not have this functionality.

Please note that both stored procedures and triggers will report errors if the temporary table does not exist.

Additional query words: Transact-SQL triggers stored procedures 


Keywords          : kbprg SSrvServer 
Version           : 4.2
Platform          : OS/2 
Issue type        :

Last Reviewed: March 9, 1999