Ken,

When we first got into SQL (using VFP front end) I wanted to do this to, write generic trigger code to do our audits. I found out it 1) wasn't easy, 2) was't worth the time to do it.

You can do it, once you have the field name you have to create SQL on the fly to select the value from the inserted table and deleted table. In addition you have to duplicate the inserted and deleted table since they are only available in the trigger and won't be avilable when you exec() your SQL. You also have to create a temp table to receive the results.

Much easier to write a trigger specific to each table so you don't have to do these SQL gymnastics. Also, you will get much better performance than going into the sysobject table every update to get field names and such.

If you want to spend time writting code that uses the sysobjects table, write a batch that will generate your triggers!

Anyway, I don't mean to discourage you, but advise you on the best approach, cause I have been there, done that.

If you still want to persue it, and need help with the steps I gave you above, let me know.

BOb


>>>Hi,
>>>
>>>Is there any way to do one of the following in a trigger?
>>>
>>>1 - return the value of a field from inserted/deleted tables where the field name is stored in a variable? i.e.

select MythicalEvalFunction(@MyField) from inserted ....

>>>
>>>2 - return the value of a field from inserted/deleted tables where all you have is the field ID (from the corresponding actual table) i .e.

select MythicalEvalFunction(field_id) from inserted ...

>>>
>>>TIA,
>>
>>Ken,
>>
>>HUH? Would be my response to your questions...
>>
>>First, what do you mean by RETURN? A trigger is called automatically, who or what are you returning the value to?
>>
>>Also, what do you mean by the field_id ??? Do you mean the object ID?
>>
>>Perhaps if you tell us what you are trying to acomplish it would be easier to get you on the right road.
>>
>>BOb
>
>I am trying to wite a trigger to log changes to a table. The "log" table contains fields for the table changed, the field changed, and the old and new values. The idea is to determine in the trigger, which fields have changed and then make an entry for each field changed into the log table. Using columns_updated() and some bit math, I can succesfully loop around and get the names of the fields that have changed into a variable. I also, at that point, have the column_id (what I previously called the field_id) of the updated field. However, I haven't yet figured an easy way to 'get' the value of the field from the inserted and deleted tables from either the column name or the column_id so I can insert it into the log table.
>
>TIA,