Using temp tables within triggers and procs

Using temp tables within triggers and procs

Post by Pete Martin » Fri, 21 Sep 2001 03:15:27



Hi...I am attempting to overcome the Sybase limitation on triggers
that prevents temp tables from being created within a trigger. So, I
have tried to call a proc from the trigger to create the temp table,
then make use of the temp table from within the trigger. The problem
seems to be that after the proc has completed execution, the temp
table disappears and the trigger can't "see" it. I also need to be
able to make use of the inserted and deleted tables that are only
available in a trigger (which I guess I can't use from a proc within a
trigger?) in conjunction with my temp table. Here is the general way I
am going about it:

create proc new_proc as
begin
create table #new_table (last_name varchar(25) null)
end

create trigger new_trigger on perm_table for insert,delete,update as
begin
exec new_proc
insert #new_table select deleted.last_name
end

My problem is that #new_table no longer exists at the insert statement
from new_trigger. Also, can I still use #new_table after the trigger
has finished?

For example can I do this:

insert perm_table values ("john","smith","St. Louis, MO")
<<trigger new_trigger executes on the insert>>
select * from #new_table

Thanks for any advice!

 
 
 

Using temp tables within triggers and procs

Post by Paul Tom » Fri, 21 Sep 2001 05:48:26




>Hi...I am attempting to overcome the Sybase limitation on triggers
>that prevents temp tables from being created within a trigger. So, I
>have tried to call a proc from the trigger to create the temp table,
>then make use of the temp table from within the trigger. The problem
>seems to be that after the proc has completed execution, the temp
>table disappears and the trigger can't "see" it. I also need to be
>able to make use of the inserted and deleted tables that are only
>available in a trigger (which I guess I can't use from a proc within a
>trigger?) in conjunction with my temp table. Here is the general way I
>am going about it:

>create proc new_proc as
>begin
>create table #new_table (last_name varchar(25) null)
>end

Try creating it as tempdb..new_table. Then you can access it after the
proc finishes. However, it will hang around until the server is
rebooted or you drop it specifically. You may have to put another
procedure to drop the temp table. Also, you may have to think of a way
to name the temp table uniquely so that other sessions don't also
try to work on the same table.

Paul
--

10000+ Humorous Quotes              http://www.tomkoinc.com/quotes.html
"Even if you can deceive people about a product through misleading statements,
sooner or later the product will speak for itself." - Hajime Karatsu

 
 
 

Using temp tables within triggers and procs

Post by Anthony Mandi » Fri, 21 Sep 2001 15:43:37



> Hi...I am attempting to overcome the Sybase limitation on triggers
> that prevents temp tables from being created within a trigger.

        Can you explain what it is you are trying to do in the trigger
        that requires a temp table?

-am     ? 2001

 
 
 

1. Creating a local temp table within an sp using Execute() or sp_executesql

Hi all
I have a stored proc that creates a bit of SQL that inserts values into a
local temporary table, something like;

Execute('SELECT PersonID INTO #TmpResults FROM Person WHERE Surname LIKE
"abc%"')

But later within the same stored proc I can't see the created #TmpResults
table;

Execute('SELECT P.* FROM Person P, #TmpResults R WHERE P.PersonID =
R.PersonID')

or even

SELECT P.* FROM Person P, #TmpResults R WHERE P.PersonID = R.PersonID

both fail with an 'Invalid object name #TmpResults' error.
I thought local temp tables were visible until the user disconnects from the
database, but I suspect its going out of scope as soon as the Execute has
completed.

Anyone got any idea how I might achieve this?

Cheers
John Hair

2. FATAL EXCEPTION_ACCESS_VIOLATION

3. Using #temp tables within begin/commit

4. DB2 Table Editor

5. Using ALTER TABLE statement within a trigger...

6. C Structs I/O

7. Output/Return Values from PROCS within PROCS

8. Security

9. trigger within procedure within trigger

10. stored procs & temp tables

11. stored procs and temp tables

12. Stored Procs, unique temp Tables, and Good Design Practice

13. Temp tables, stored procs and ASP