Dynamically finding column values in a trigger from INSERT when the column name is a variable

Question

I have a trigger that gets inserts and updates to a view. I need to find the columns that are being modified and get the value into the correct table.

INSERT INTO TempTableAttr_Lot(ID, [% Num]) VALUES(3, 24.0)

I am trying to figure out how, in my trigger, to get the value of ID and [% Num] columns. The problem is that there can be 32 different columns that are set in the insert or update, so I want to loop through looking to see if that column is in the 'inserted' table.

One problem is that if I use exec or execute, to build a dynamic query, inserted is not in scope for this.

The view is dynamic in that if attributes are added then the view is regenerated by a stored procedure, so I can't assume what column names are in the view at any given time, it can grow and shrink.

Ideally I would like to do SET @Value = (SELECT i.[@Name] FROM inserted i) But @Name is not a column name, but a variable in my trigger, so when I tried

DECLARE @ValueTable TABLE ( value sql_variant)
INSERT INTO @ValueTable EXECUTE('SELECT i.[' + @Name + '] FROM inserted i')
SET @Value = CONVERT(nvarchar(128), (SELECT DISTINCT * FROM @ValueTable))

this didn't work as inserted appears to not be in scope.

For the first loop @Name = 'ID' and for the second it will be 'Col2' which was not inserted, so the value of @Value should be null, and I continue to loop through the possible column names until I finish, and the trigger is processed.

I am calling it a trigger, it is defined as:

CREATE TRIGGER TempTableAttr_LotTrigger 
ON  TempTableAttr_Lot 
INSTEAD OF UPDATE, INSERT

I am testing with an INSERT command at the moment. I realize that when doing an update I may need to look at the 'deleted' table.

UPDATE: I am assuming only one row is being inserted at the moment, for my test, to see how to get this to work. This is for an entity-attribute-value database, but I have a view that makes it look relational. When using Integration Service, when the updating is going on, any inserts or updates to the view will call the trigger. I am just trying to understand how to get the information I need so I can update the correct table, with the correct values.

Answer 1

To get access to the INSERTED table you can do the following.

SELECT * INTO #MYINSERTED FROM INSERTED

EXEC('SELECT * FROM #MYINSERTED')

DROP TABLE #MYINSERTED