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.
| Author |
Topic |
|
rmh
Starting Member
7 Posts |
 Posted - 2005-03-03 : 13:25:12
|
| Dear all:In a trigger:SELECT @st = MyCol FROM insertedloads the var @st with the value in the [first?] row of the column MyCol from the 'inserted' virtual table, as one would expect.So now I have a variable @MyColName loaded with the constant 'MyCol' but SELECT @st = @MyColName FROM inserted returns 'MyCol', not the value in the column, in other words @MyColName isn't evaluating. Anyone know how to do this? THANKS! |
|
|
lazerath
Constraint Violating Yak Guru
343 Posts |
Posted - 2005-03-03 : 13:43:50
|
I don't know your business problem and I could help you more if I did. I believe this is what you are looking for, but I can almost guarentee you are better off taking a different approach:DECLARE @MyStringToEval NVARCHAR(10), @SQL NVARCHAR(500), @MyOutput INT;SELECT @MyStringToEval= '12/4', @SQL= 'SELECT @Result = ' + @MyStringToEval;EXEC sp_executesql @SQL, N'@Result INT OUT', @MyOutput OUT;SELECT @MyOutput; |
 |
|
|
TG
Master Smack Fu Yak Hacker
6065 Posts |
Posted - 2005-03-03 : 14:39:29
|
Are you trying to write a "generic" trigger you can put on any table? Probably not a good idea. What are you trying to accomplish?quote:
loads the var @st with the value in the [first?] row of the column MyCol from the 'inserted' virtual table, as one would expect.
Actually, the variable will be set 1 time for each row in the table. Whichever the last row evaluated happens to be is the value that the variable ends up being set to.Be One with the OptimizerTG |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-03 : 14:42:34
|
| Except that the inserted virtual table isn't available in the executed batch.could doselect * into #a from inserted then reference that as long as there are no blobs.Your initial comments are probably more applicable.==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rmh
Starting Member
7 Posts |
Posted - 2005-03-03 : 15:34:41
|
| Thanks for all your responsesYes, it is almost certain I am doing the wrong thing at the moment but I am experimenting. My objective is to do away with a mass of application code (in CF) which maintains an audit... but I need something a bit more clever than I've found in google so far.Essentially I was thinking of using a lookup table from my trigger, containing the column names I am interested in maintaining an audit on, and some extra parameters. The objective is to:a) only auditit the columns I need to.b) being able to make them readable by the user, so I have in my lookup table "Working title changed from" against the field OPTJOBNAME which I can use in generating my audit.c) know, from my lookup table about data types so a bit type field, for example is recorded as having changed 'from no to yes' rather than '0 to 1'd) concantenate all changes to an instance of a change in a row in my data table into a single row in my audit table, separated by line breaks.so if anyone has any ideas or code out there I should be more than grateful, as you've probably noticed my T-SQL skills aren't the best... |
|
|
|
rmh
Starting Member
7 Posts |
Posted - 2005-03-03 : 15:41:46
|
| Oh - and I should say the sort of changes I am interested in auditing are invariably made one row at a time in my data table so I don't think I am too constrained by the notion of having to deal with multiple rows in 'inserted' or 'deleted' |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2005-03-03 : 15:46:12
|
| Have a look athttp://www.mindsdoor.net/#Triggers==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
|
|
evaluating variables