How To Override DML Statements with Triggers in SQL Server?

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 fyi_users to validate email addresses:

USE FyiCenterData;
GO

CREATE TRIGGER check_email ON fyi_users
INSTEAD OF UPDATE
AS
  DECLARE @count INT;
  SELECT @count = COUNT(*) FROM INSERTED
    WHERE email NOT LIKE '%_@_%';
  IF @count = 0
    UPDATE fyi_users SET email=i.email
      FROM INSERTED AS i
      WHERE fyi_users.id = i.id
  ELSE
    PRINT 'Invalid email(s) found.';
GO

-- invalid email
UPDATE fyi_users SET email='john.king'
  WHERE name = 'John King';
GO
Invalid email(s) found.

-- valid email
UPDATE fyi_users SET email='john@fyicenter'
  WHERE name = 'John King';
GO
Email changed from gniK nhoJ to john@fyicenter

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

The "invalid email" test shows that trigger check_email did stoped 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.

⇒ "CREATE TRIGGER" - Creating a DDL Trigger in SQL Server

⇐ Triggers with Multiple Affected Rows in SQL Server

⇑ Creating and Managing Triggers in SQL Server

⇑⇑ SQL Server Database Tutorials