Test for column name then select from it in trigger.

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 2000 Forums

SQL Server Development (2000)

Test for column name then select from it in trigger.

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-04-17 : 09:33:19

Dave Harkness writes "We are writing a trigger that checks the sysobjects table for the existence of a column in a table, then runs the appropriate query.

If EXISTS (select a.name from syscolumns a, sysobjects b
where a.id=b.id and b.name = 'table_name'
and a.name=column_name)
BEGIN
Select column_name from table_name
END
ELSE
BEGIN
Select another_col_name from table_name
END

When we try to create the trigger in SQL Analyzer, the parser complains that column_name does not exist in the table? Well, yea, we know that. That is why I put the check in.

Why is this? Any help would be appreciated....

Thanks in advance,
Dave Harkness
dave.harkness@imark.com"

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-04-17 : 09:58:39

You would have to write this trigger using dynamic SQL to put the (variable) colum name into the SELECT portion (why are using SELECT in a trigger anyway? Is this a real trigger, or a job?)

You can try these articles:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

They'll get you started.

hark1204
Starting Member

2 Posts

Posted - 2002-04-17 : 11:12:48

Thanks for replying..

This is only part of the trigger. I guess I should have been more specific.
We have an on insert trigger that selects from the INSERTED table the values that were just inserted. We put those values in another table and do other things. We cannot mess with the originating application.
The actual error is associated with selecting a column from the INSERTED table. It complains that the column does not exist in the INSERTED table at trigger creation time even though the INSERTED table does not yet exist. We check the base table (there are a number of them) for the columns existence then execute a query based on that result. The way I understand it, you cannot build dynamic sql accessing the INSERTED table since sp_executesql is a stored procedure running in its own process space and knows nothing about the INSERTED table.

Dave Harkness
dave.harkness@imark.com

quote:

You would have to write this trigger using dynamic SQL to put the (variable) colum name into the SELECT portion (why are using SELECT in a trigger anyway? Is this a real trigger, or a job?)

You can try these articles:

http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

They'll get you started.

Dave

motokevin
Starting Member

36 Posts

Posted - 2002-04-17 : 12:18:21

Here's how to run using dynamic SQL.
Also, don't rely on the syscolumns or sysobjects tables. It's dangerous and you might not make it back.
Use the Information_Schema that is set up for you instead.

Happy Coding.

Declare @table_name varchar(25)
Declare @column_name varchar(25)
Declare @another_column_name varchar(25)
Declare @query_to_run varchar(200)

Set @column_name='name'
Set @another_column_name='another_name'

If EXISTS(
Select 1
From Information_Schema.Columns
Where Table_Name = @table_name
And Column_Name = @column_name)
BEGIN
Set @query_to_run = 'Select ' + @column_name + ' from ' + @table_name
END
ELSE
BEGIN
Set @query_to_run = 'Select ' + @another_column_name + ' from ' + @table_name
END

EXEC(@query_to_run)

Edited by - motokevin on 04/17/2002 12:38:24

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources