Sql-server – before update trigger on sql server

sql servert-sqltrigger

I have two tables called users and formerpasswords. In users I have two fields: name which is primary key and password. In formerpasswords I have three fields: number which is primary key, name and password. I would like to create a trigger on users table so if I insert pairs of values in users table for name and password fields and after I update the value for password field where name = some of the inserts I have made in users table, trigger fires and inserts old value of name and password in formerpasswords table. How could I do that in SQL Server? I know that this can be done easily in MySQL using a before update trigger but in SQL Server that doesn't exist so I look for a way to do this.

Thank you so much in advance.

Best Answer

Here is a functional (albeit basic) example for you. Triggers in SQL Server can be either AFTER or INSTEAD and can apply to any DML (INSERT, UPDATE or DELETE), even at the same time. Inside the scope of each trigger is a virtual table called:

  • INSERTED - what is intended to be.
  • DELETED - what the old values were.

These virtual tables contain every column from the base table and make modification to either the base table or other tables.

CAVEATS (or basic reminders):

  • MAKE SURE that the statement inside your trigger can handle multiple rows properly. A common mistake that I see is people making the logic inside the trigger only work for one row at a time.
  • These can kill performance, use them sparingly and keep the logic as simple as possible.
  • Bulk Inserts do not typically fire triggers, but can be configured to do so.
  • AVOID cursors inside triggers.
  • You cannot try/catch inside a trigger. Any exception that happens in there WILL bubble out to the caller.

FINALLY

Take care storing the passwords using a proper cryptographic library. I used VARCHAR in my example below to better illustrate the trigger logic.

DROP TABLE IF EXISTS dbo.FormerPasswords 
DROP TABLE IF EXISTS dbo.Users 

GO

CREATE TABLE dbo.Users
    (
    UsersID INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UserName NVARCHAR(200) NOT NULL
    , CurrentPassword VARCHAR(64) NOT NULL
    )

CREATE TABLE dbo.FormerPasswords
    (
    FormerPasswords INT NOT NULL IDENTITY(1,1) PRIMARY KEY
    , UsersID INT NOT NULL REFERENCES dbo.Users (UsersID)
    , PriorPasswords VARCHAR(64) NOT NULL
    , DateLastUsed_UTC DATETIME2(7) NOT NULL DEFAULT (SYSUTCDATETIME())
    )

GO

CREATE OR ALTER TRIGGER trgUsers 
    ON dbo.Users
    AFTER UPDATE
AS
BEGIN

    INSERT INTO dbo.FormerPasswords
    (UsersID, PriorPasswords)
    SELECT D.UsersID
        , D.CurrentPassword
    FROM DELETED AS D

END

GO

INSERT INTO dbo.Users 
(UserName, CurrentPassword)
VALUES ('jsmith', 'original 1')
    , ('jdoe', 'original 2')

UPDATE dbo.Users 
SET CurrentPassword = 'updated 1'
WHERE UserName = 'jsmith'

SELECT * FROM dbo.Users 
SELECT * FROM dbo.FormerPasswords
Related Question