April 15, 2017 at 8:43 pm
I hope someone can give me suggestions on how to handle this situation of triggers.
An application will be inserting and updating records into a single table, also there will be several date (Creat_dt, Updt_dt) fields on the statement coming from the application. I don't want to use the values provided on the Insert and Update statement, I want to override the values and use getdate() instead, as to properly represent when the record was inserted or updated in the target table. NOTE: It's already been discussed the source application cannot be changed to not included these fields, to bad for me.
I believe the solution involves using triggers and I've tried using the AFTER INSERT trigger then firing an UPDATE statement setting CREAT_DT = getdate() and UPDT_DT = null, this then fires an UPDATE trigger if there is one. I believe I need an UPDATE trigger to handle the same situation for incoming updates from application, ignoring the incoming values, but I see a potential conflict with the INSERT trigger and it's UPDATE statement. Is there way to handle this in the update trigger? One more thing I thought maybe an INSTEAD OF for the INSERT might work, but it seems examples show that you must reconstruct the INSERT statement in the trigger, my table has 100 field and I'd like not to have to hardcode the insert col1, col2, .... ,
UPDATE datetime_test
SET datetime_test.CREAT_DT = CURRENT_TIMESTAMP
, datetime_test.CREAT_BY = SYSTEM_USER
, datetime_test.UPDT_DT = null
, datetime_test.UPDT_BY = null
FROM dbo.datetime_test
INNER JOIN inserted AS i
ON datetime_test.ID = i.ID;
April 16, 2017 at 4:02 pm
In your update trigger you can use IF UPDATED(ColumnName) to check whether something other than the created and updated dates were updated, and only proceed with the trigger if other columns were updated. That way, the update in your insert trigger fires the after update trigger, but the check fails and nothing happens.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply