Sometime, you may want to implement some business logics in a DML trigger to cancel the DML statement. For example, you may want to check the new email address format provided by the UPDATE statement. If the email address is invalid, you to cancel the UPDATE statement.

There is no easy way to cancel the DML statement in a DML trigger. But there is easy way to override the DML statement with an "INSTEAD OF" trigger. SQL Server supports 2 options (3 keywords) on when the defined trigger will be fired:

* AFTER - Trigger fired after the DML statement executed successfully.

* INSTEAD OF - Trigger fired instead of the DML statement execution, allowing the trigger to decide whether or not, and how, to execute the statement.

* FOR - Same as AFTER.

The tutorial exercise below shows you how define an "INSTEAD OF" trigger on ggl_users to validate email addresses:

CREATE TRIGGER check_email ON ggl_users

INSTEAD OF UPDATE

AS

DECLARE @count INT;

SELECT @count = COUNT(*) FROM INSERTED

WHERE email NOT LIKE '%_@_%';

IF @count = 0

UPDATE ggl_users SET email=i.email

FROM INSERTED AS i

WHERE ggl_users.id = i.id

ELSE

PRINT 'Invalid email(s) found.';

GO

-- invalid email

UPDATE ggl_users SET email='john.king'

WHERE name