ID Number: Q65756
1.00 1.10 1.11 4.20
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).
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 reference words: Transact-SQL triggers stored procedures