[thelist] SQL Variables

Jonathan Cone JCone at municode.com
Thu Jan 23 16:30:01 CST 2003

	we are trying to create an auditing system to keep up with what has changed in the table.  We have set up the audit table to receive all requested variables, user, time, previous value, new value, etc...
	So we set up a trigger on the server so that anytime that particular table that the trigger resides on is updated the trigger runs and dumps all the variables into the audit table.
	However, Triggers (and this is where I may be messing up) seem to require a field name also.  So from what I can gather you have to set up a specific trigger or set of code for every field in the table.
	Thinking this through we tried to use some dynamic SQL to determine which field is being updated and run the trigger for that field.  It seems that you can use variables within your select statement for example:

	Select * from @table_variable

However, it blows up when you try to use a variable with the field.  For Example:

	Select @field_variable from @table_variable

You can get around these errors we've found by putting the select statement into an Exec:

Exec( ' Select ' + @field_variable + ' from ' + @table_variable)

Which works!
However we need the results of the query (which returns ONE value) dumped into another variable which we put into the Audit table.

Does anyone have any experience with this?  what's happening here?  Help??

More information about the thelist mailing list