Post reply

SQL Server Triggers.

  • 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.

     

     

     

  • August 29, 2019 at 12:01 pm

    #3675382

    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)

  • August 29, 2019 at 1:22 pm

    #3675429

    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.

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

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