INF: Using Temporary Tables with Stored Procedures/Triggers

 Last reviewed: April 25, 1997
Article ID: Q65756

The information in this article applies to: 

  - Microsoft SQL Server version 4.2 for OS/2

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

THE INFORMATION PROVIDED IN THE MICROSOFT KNOWLEDGE BASE IS PROVIDED "AS IS" WITHOUT WARRANTY OF ANY KIND. MICROSOFT DISCLAIMS ALL WARRANTIES, EITHER EXPRESS OR IMPLIED, INCLUDING THE WARRANTIES OF MERCHANTABILITY AND FITNESS FOR A PARTICULAR PURPOSE. IN NO EVENT SHALL MICROSOFT CORPORATION OR ITS SUPPLIERS BE LIABLE FOR ANY DAMAGES WHATSOEVER INCLUDING DIRECT, INDIRECT, INCIDENTAL, CONSEQUENTIAL, LOSS OF BUSINESS PROFITS OR SPECIAL DAMAGES, EVEN IF MICROSOFT CORPORATION OR ITS SUPPLIERS HAVE BEEN ADVISED OF THE POSSIBILITY OF SUCH DAMAGES. SOME STATES DO NOT ALLOW THE EXCLUSION OR LIMITATION OF LIABILITY FOR CONSEQUENTIAL OR INCIDENTAL DAMAGES SO THE FOREGOING LIMITATION MAY NOT APPLY.

Last reviewed: April 25, 1997
© 1998 Microsoft Corporation. All rights reserved. Terms of Use.