September 07, 2010

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))
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

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

ON emp
IF (SELECT max(salary) from deleted) > 100000
RAISERROR ('The salary exceeds 100000 ', 16, 10)

Now see what happens if salary is updated with 150000

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.

Did you like this post?
kick it on
subscribe via rss subscribe via e-mail
print this post follow me on twitter

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 and a moderator at His T-sql blog is at



1 Response to "AFTER UPDATE Trigger in SQL Server"
  1. Yurtdışı Eğitim said...
    September 9, 2010 at 1:43 PM

    I'm looking for was information. Thank you.


Copyright © 2009-2014 All Rights Reserved for by Suprotim Agarwal | Terms and Conditions