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.
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
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())
CREATE OR ALTER TRIGGER trgUsers
INSERT INTO dbo.FormerPasswords
FROM DELETED AS D
INSERT INTO dbo.Users
VALUES ('jsmith', 'original 1')
, ('jdoe', 'original 2')
SET CurrentPassword = 'updated 1'
WHERE UserName = 'jsmith'
SELECT * FROM dbo.Users
SELECT * FROM dbo.FormerPasswords