Error at using temporary tables in triggers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Error at using temporary tables in triggers

Author

Topic

balazec
Starting Member

3 Posts

Posted - 2012-07-03 : 02:54:51

The code bellow is correct, but leads to this message :

(1 row(s) affected)
Msg 207, Level 16, State 1, Procedure UpdateTrigger_table2, Line 7
Invalid column name 'B'.

In the case "SELECT * FROM #temp" instead of "SELECT B FROM #temp"
the server works correctly
( output is B = 2 )

Do you have any explanation ?

Ludo Balazec

-------------------------------------------------------------

CREATE TABLE table1( X int )

GO

CREATE TABLE table2( Y int )

GO

CREATE TRIGGER UpdateTrigger_table1 ON table1 FOR UPDATE AS
BEGIN
SELECT 1 AS A INTO #temp
UPDATE table2 SET Y = 0
END

GO

CREATE TRIGGER UpdateTrigger_table2 ON table2 FOR UPDATE AS
BEGIN
SELECT 2 AS B INTO #temp
SELECT B FROM #temp
END

GO

UPDATE table1 SET X = 0

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-03 : 04:23:41

>> The code bellow is correct
No it isn't - and that's why you are getting the message.

The server tries to resolve objects when the trigger is run. Presumably there is another table named #temp at this point (maybe one yoou created to test or in the firing procedure?) which it uses. This table dodesn't have a column B so you get the error. With select * it uses current version of the temp table when the statement runs - bit surprised that dodesn't give an error too.

oh - the other version of the temp table is frrom the insert trigger.
This is not a very good idea if yoou can avoid it but just rename one of the tables to get rid of the error.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

balazec
Starting Member

3 Posts

Posted - 2012-07-03 : 05:58:07

> This is not a very good idea if yoou can avoid it but just rename
> one of the tables to get rid of the error.

I do not agree. When I am writting a trigger I should not be obligatory to know names of temporary tables in another triggers. Trigger should encapsulate its objects but it does not it in this case !
I think, when I use "*" server works OK because it uses the right table, but if I name the field, server looks at the structure of the wrong table. In my opinion, it is error.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2012-07-03 : 07:18:24

It will happen when SQL Server complies it. Try second part with dynamic sql

EXEC
('
CREATE TRIGGER UpdateTrigger_table2 ON table2 FOR UPDATE AS
BEGIN
SELECT 2 AS B INTO #temp
SELECT B FROM #temp
END
')

Madhivanan

Failing to plan is Planning to fail

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-07-03 : 11:18:15

quote:
Originally posted by balazec

> This is not a very good idea if yoou can avoid it but just rename
> one of the tables to get rid of the error.

I do not agree. When I am writting a trigger I should not be obligatory to know names of temporary tables in another triggers. Trigger should encapsulate its objects but it does not it in this case !
I think, when I use "*" server works OK because it uses the right table, but if I name the field, server looks at the structure of the wrong table. In my opinion, it is error.

It's not about knowing the names of object in other triggers per se. SQL has the concept of a batch. If you try to create temporary object of the same name within a batch, then you will have problems, just as you are having now.

nigelrivett
Master Smack Fu Yak Hacker

3385 Posts

Posted - 2012-07-03 : 12:51:45

When a procedure is compiled it has to resolve the objects.
For a temp tabl it will resolve to an existing object in preference to one that is created in the procedure. The temp teble created has a sequence number included in it's internal name.
When the procedure comes to run it uses the temp table with the greatest sequence number.

It's not a good way for the server to work I agree but it's due to a late resolution change that as made a few versions ago and is something we have to live with.
You'll find that you can insert into a temp table that has been declared in a calling batch and it's to do with the same process. Can especially cause problems if you try to test something an run the create temp table statement then create the SP on the same spid.
The trigger dodes encapsulte it's objects - it's the object resolution before the trigger runs that is causing the issue.

You might not like it but it's something you have to live with - it can come in useful at times.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

balazec
Starting Member

3 Posts

Posted - 2012-07-03 : 13:22:51

OK, but 2 more notes:
a) It is not an artificial problem, in real situation ( much more complicated then example which I have constructed here ) I did not want to confuse server, I only solved my work problem by trigger which used a temporary table. In presumption of encapsulation of objects I could not wait conflict with names in another trigger ...
b) If I update table2 first, server "realizes its mistake" and next updates table1 work correctly ...

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources