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
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
IF (SELECT max(salary) from deleted) > 100000
RAISERROR ('The salary exceeds 100000 ', 16, 10)
Now see what happens if salary is updated with 150000
The result is
Note that if you don’t use rollback, you get an alert but the value will be updated to the table.