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

Read more: SQL Server curry

Posted via email from .NET Info