CODEWITHSUNDEEP
sql-servertriggers
MrSimpleMind
MrSimpleMind

Reputation: 8597

Force column update to a specific value when changed

I want to trigger an update on a specifc column, when changed.

(history: some application does an update on a column, and I cant find what app. Therefore I decieded to make a trigger to force back the value).

To make it simple.. 

UsrTbl:
usrid usr   pwd
1001  admin qwerty
2001  cto   demo
3001  ceo   demo
...

if someone makes an update on pwd where usr is admin, I want to re-update it to specific value.

If i do a trigger, something like:

CREATE TRIGGER the_usr_trg ON usrtbl AFTER UPDATE AS
UPDATE usrtbl SET pwd='qwerty' WHERE usr = 'admin'
GO

Will above fix this for me? What happen when the trigger updates the column? will it fire the_usr_trg trigger again ? will this cause a loop in the database server? or will it run once only? Is there better approach to fix this? (other than finding what app is updating this column:)

Thank you!

Upvotes: 0

Views: 5172

Answers (3)

kaj
kaj

Reputation: 5251

Well as per @cadrell0's comment, you can just test it in a dev environment to see whether it will recurse.

A bigger issue is that you're just papering over the cracks by resetting the value rather than finding out the root cause - look at things like the system view sys.trigger_events or implementing an audit table (which is a more typical way to use a trigger) to see when and why the value is changed.

Ignoring the fact that it's so bad to store passwords in plain text...

To avoid recursion in your trigger you can use the inserted table to see the changes proposed or use the trigger update() function to see if the column has been changed. If the column is already set to the value you want, you don't need to update it again.

Upvotes: 0

Vince Pergolizzi
Vince Pergolizzi

Reputation: 6584

Triggers by default do not call themselves recursively unless you tell them to by using the following statement:

SET RECURSIVE_TRIGGERS ON

Your code should work fine.

Upvotes: 1

Lamak
Lamak

Reputation: 70658

I believe that it will depend on the recursion level for triggers that is on the DB. If the RECURSIVE_TRIGGERS is setted to OFF, then it will be run only once. In your case, think that it would be better if you create an INSTEAD OF trigger, thus avoiding the first UPDATE on your table. In your case it would be something like this:

CREATE TRIGGER the_usr_trg ON usrtbl 
INSTEAD OF UPDATE 
AS
BEGIN

    UPDATE A
    SET pwd= B.pwd
    FROM usrtbl A
    INNER JOIN INSERTED B
        ON A.usr = B.usr
    WHERE usr != 'admin'

END

Upvotes: 1

Related Questions