AFTER UPDATE Trigger in SQL Server

AFTER triggers can be used to fire after updating a table. It can be used to make decisions based on the values being updated.

Consider the following table:

CREATE TABLE emp(empid varchar(10), salary decimal(12,2))
GO
INSERT INTO emp(empid,salary)
SELECT 'EMP01',20000 union all
SELECT 'EMP02',16700 union all
SELECT 'EMP03',2000 union all
SELECT 'EMP04',2800.45 union all
SELECT 'EMP05',50000
GO

Suppose you don’t want anyone to update the salary of an employee more than 100000. In this case, you can use after update trigger as shown below

CREATE TRIGGER alert_me
ON emp
AFTER UPDATE
AS
IF (SELECT max(salary) from deleted) > 100000
 RAISERROR ('The salary exceeds 100000 ', 16, 10)
ROLLBACK
GO

Now see what happens if salary is updated with 150000

UPDATE emp
SET salary=1500000
WHERE empid='EMP01'

The result is

Note that if you don’t use rollback, you get an alert but the value will be updated to the table.

About The Author

Madhivanan,an MSc computer Science graduate from Chennai-India, works as a works as a Lead Subject Matter Expert at a company that simplifies BIG data. He started his career as a developer working with Visual Basic 6.0, SQL Server 2000 and Crystal Report 8. As years went by, he started working more on writing queries in SQL Server. He now has good level of knowledge in SQLServer, Oracle, MySQL and PostgreSQL as well. He is also one of the leading posters at www.sqlteam.com and a moderator at www.sql-server-performance.com. His T-sql blog is at http://beyondrelational.com/blogs/madhivanan