TSQL- Trigger instead of UPDATE column not included in update statment

Question

I have a small issue with what I am trying to do. but I suspect that it is not possible and just would like some other insights.

I have a table that contains six columns. two of which are related to recording updates on the table,'UpdateBy' and 'UpdateDate'. when some one applies an update on the table the update date is set to a system time regardless of whether someone tries to set the time and date or not (no problem here) but when it comes to the 'UpdatedBy' I have an issue. I have tried to use the 'inserted' table from within the trigger but when a statement is executed.

update PoolGroups
set
    PoolDescription = 'test 1 description'
where
    PoolName = 'test 2'

that doesn't contain any entry for the 'UpdatedBy' column the 'Inserted.UpdatedBy' field contains what is already in the table not what was passed in using the 'Update' statement. but the 'Inserted.PoolDescription' has the changed text not the original. here is the trigger.

CREATE TRIGGER TR_PoolGroups_Update
ON  PoolGroups
instead of UPDATE
AS 
BEGIN       
    UPDATE PoolGroups 
        SET  
            PoolDescription = i.PoolDescription 
            ,UpdatedBy =  coalesce(i.updatedby , (select SUSER_NAME()))
            ,UpdatedDate = getdate()
        FROM 
            PoolGroups pg
            INNER JOIN Inserted i 
                ON i.PoolName = pg.PoolName
END
GO

Data already in table PoolGroups.

PoolName        PoolDescription     UpdatedBy  UpdatedDate
test 2            test description    test1      2013-06-22 14:39:55.930

Data in Inserted table in trigger when insert is run.

PoolName        PoolDescription     UpdatedBy  UpdatedDate
test 2          test 1 description  test1      2013-06-22 14:39:55.930

I was hoping just to catch when the update statement didn't contain the 'UpdatedBy' field and then replace this with the logged in user name. my problem is that I cant work out how to do detect whether this field was or was not included in the update statement that is run against this table as I would have expected the inserted table to have a null in the 'UpdatedBy' field as it was not specified in the update. is there another way I can find out if it was specified in the update?

score 2 · Accepted Answer · answered Jun 23 '13 at 06:16

I think I have found my answer. with the use of the 'Update(columname)' function I can determine whether the field I was having problems with has been updated in the update statement or not. so by just replacing the 'coalesce' statement with a case statement has sorted my issue. new Trigger Code.

CREATE TRIGGER TR_PoolGroups_Update
    ON  PoolGroups
    instead of UPDATE
    AS 
    BEGIN   
        UPDATE PoolGroups 
            SET  
                PoolDescription = i.PoolDescription 
                ,UpdatedBy =  case when UPDATE(UpdatedBy) 
                                   then i.updatedby 
                                   else (select SUSER_NAME()) end
                ,UpdatedDate = getdate()
            FROM 
                PoolGroups pg
                INNER JOIN Inserted i 
                    ON i.PoolName = pg.PoolName
    END 
go

Now if someone applies an update to the table they don't have to specify the 'UpdatedBy' as by default it will now pickup the SQL username.

TSQL- Trigger instead of UPDATE column not included in update statment

1 Answers1