dynamic Instead of update trigger problem

Hello,

I am trying to use an instead of update trigger to alter a lastupdated field in the table when any of the other fields are modified by an update. I want to do this as I can't rely on the applications updating the date when they alter other data. The trigger looks something like this.

CREATE TRIGGER [noidentitytableinsteadupdate] ON [dbo].[noidentitytable]

INSTEAD OF UPDATE

AS

if(not update(updatedat))

begin

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = GetDate()

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

end

else

update noidentitytable

set text = inserted.text,

state = 'updated',

updatedat = inserted.updatedat

from noidentitytable

INNER JOIN inserted ON (noidentitytable.id = inserted.id)

The problem I am facing is when you add say another 10 fields to the table, and I want to ONLY do an update to the fields that have been updated by the original statement. In effect I want to check each column using update(column) and include it in the new update statement along with the lastupdate field. Dynamic sql does not have access to the inserted table so how would you do this?

The reason I want to exclude all the unmodified fields is because of existing after triggers that fire based on changes to individual column changes. I tried this in an after trigger originally but as this is done as an additional update this causes multiple fires of other triggers.

If the original update was

update noidentitytable set text = 'hello', state = 'updated',

but there were actually 10 columns in the table then the actual update I want to do is.

update noidentitytable set text = inserted.text, state = 'updated', updatedat = GetDate() from noidentitytable INNER JOIN inserted ON (noidentitytable.id = inserted.id)

Or perhaps what I really want to do is get hold of the original SQL and insert text change the datefield.

Can someone enlighted me please?

It would definitely be better to do this in an after trigger. Can't you just add a condition to all the other after triggers to do nothing if the updatedate column is the one being updated?|||

Why would you want to reissue the UPDATE statement again? It takes lot of work to perform an update and you will just make the entire UPDATE statement slower. It seems like you should just create a SP that performs the necessary UPDATE statement, add default on the updatedat column and use DEFAULT keyword in the UPDATE statement. This will be much more scalable, run more efficiently and easy to manage.

|||

I understand that you have to issue the update statement yourself in a instead of trigger, or am I missing a way of committing the original statement first?

I do use the default column value for the date, however setting the date to the default on the update is not much different than setting to GetDate().

what I really wanted was all the values that have changed from the inserted table plus some other default information ie date, user who modified.

|||

Perhaps I should give a different scenario that would require the same solution.

If I was using an instead of update trigger on a view and the underlying table structure changed but to remain backward compatible I left the view definition the same how would you go about ensuring only the fields that the client was updating were updated correctly in the underlying tables?

I would assume this would require some dynamic sql to check the updated columns and then update the tables underneath. Surely you would not update every column even if the value had not changed?