SQL Server Triggers.

Question

SQL Server Triggers.

satnam.singh 44358

Ten Centuries

Points: 1300
More actions
August 29, 2019 at 8:47 am

#3675337

I have a table which has around 8 columns out of which 4 are CreatedBy,CreatedDate,ModifiedBy,ModifiedDate
I have an INSERT TRIGGER which updates only the columns named CreatedBy and CreatedDate; an Update Trigger which updates only the columns named ModifiedBy and ModifiedDate.
Now, i need to ensure that CreatedBy,CreatedDate,ModifiedBy,ModifiedDate should only be updated by the AFTER INSERT,UPDATE Trigger but user shouldn't be able to update these 4 columns.
Can anyone please advise on How this can be accomplished.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1003863 More actions · Answer 1

My first recommendation would be to stop using such homegrown methods. Temporal tables work a fantastic treat and the audit table they leave behind is pretty much immutable with virtually no effort on your part. They also leave a much better audit table, and will make your queries that result in scans much faster because they'll only have to weed through the original 4 columns instead of 8. It will also save you from having the CREATED* columns and only have a need for the MODIFIED* columns because rows prior to changes will be stored in the temporal table.

If you don't want to do that, then you'll need to change your triggers to detect and reject any changes to your 4 "LMB" columns using the UPDATED() function in triggers.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

nova SSC Journeyman Points: 80 More actions · Answer 2

DENY UPDATE (CreatedBy, CreatedDate, ModifiedBy, ModifiedDate) ON tbl TO xyz;

Where xyz is the user or role that has to be denied update permissions.

But I would agree with Jeff's suggestion because triggers are a poor way to do this. I suggest you avoid using triggers that modify data.