executing a string build query on 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 2005 Forums

Transact-SQL (2005)

executing a string build query on trigger

Author

Topic

myrbs
Starting Member

10 Posts

Posted - 2010-05-14 : 03:21:38

Hi I have build a string that would be executed on a trigger
the problem is when i execute it, it would return an error saying

"Could not find stored procedure "
the builded string is 'SELECT username+logs,'A','tbl_bet_bid_log',getdate() from inserted'

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-14 : 03:49:39

Why are you building it dynamically?
Post the full code you used

Madhivanan

Failing to plan is Planning to fail

myrbs
Starting Member

10 Posts

Posted - 2010-05-14 : 04:06:08

CREATE trigger sample_trigger
on tbl_bet_bid_log
FOR INSERT
AS
DECLARE @columns VARCHAR(MAX)
SET @columns = (select COLUMN_NAME = stuff((select '+' + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS x
where x.TABLE_NAME = c.TABLE_NAME
order by ORDINAL_POSITION
for xml path('')), 1, 1, '')
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = 'tbl_bet_bid_log'
group by TABLE_NAME)

DECLARE @temp VARCHAR(MAX)
SET @temp = 'SELECT '''','+@columns+','+'''A'',''tbl_bet_bid_log'',getdate() from inserted'

SET @temp ='INSERT audit_log (old_val,new_val,[type],table_name,date_maint)'
+ @temp
exec @temp

Im building it dynamically because I dont want to list all the columns of the table

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-14 : 05:05:38

exec @temp

should be

exec (@temp )

Madhivanan

Failing to plan is Planning to fail

myrbs
Starting Member

10 Posts

Posted - 2010-05-14 : 05:07:32

same it still has error, saying inserted is an invalid object name

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2010-05-14 : 05:21:38

You need this

CREATE trigger sample_trigger
on tbl_bet_bid_log
FOR INSERT
AS
DECLARE @columns VARCHAR(MAX)
SET @columns = (select COLUMN_NAME = stuff((select '+' + COLUMN_NAME
from INFORMATION_SCHEMA.COLUMNS x
where x.TABLE_NAME = c.TABLE_NAME
order by ORDINAL_POSITION
for xml path('')), 1, 1, '')
from INFORMATION_SCHEMA.COLUMNS c
where TABLE_NAME = 'tbl_bet_bid_log'
group by TABLE_NAME)

INSERT audit_log (old_val,new_val,[type],table_name,date_maint)
SELECT '',@columns,'A','tbl_bet_bid_log',getdate() from inserted

Madhivanan

Failing to plan is Planning to fail

myrbs
Starting Member

10 Posts

Posted - 2010-05-14 : 05:37:26

the @columns is builded to get all columns of the table, then on insertion i will select the value of the columns returned by @columns, so if i would use this, it would insert the name of the column not the value of those columns

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources