MS SQL Server Interview Question:

How To Override DML Statements with Triggers?

Submitted by: Administrator

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
Submitted by: Administrator

-- valid email
UPDATE ggl_users SET email='smith@GlobalGuideline'
WHERE name = 'John King';
GO
Email changed from gniK nhoJ to john@ggl

-- change all
UPDATE ggl_users SET email='dba@ggl';
GO
Email changed from ramuK craM to dbaggl@GlobalGuideline
Email changed from hsuB yoR to dbaggl@GlobalGuideline
Email changed from etaG kcaJ to dbaggl@GlobalGuideline
Email changed from grebneerG ycnaN to dbaggl@GlobalGuideline
Email changed from john@ggl to dba@ggl

The "invalid email" test shows that trigger check_email did stopped the UPDATE statement. John King's email did not get updated.

The "valid email" test shows that trigger check_email properly updated the email column, if the new email is valid. The reported message was generated from trigger update_user.
Submitted by: Administrator