Home >> FAQs/Tutorials >> SQL Server FAQ

SQL Server FAQ - "INSTEAD OF" - Overriding DML Statements with Triggers

By: FYIcenter.com

(Continued from previous topic...)

How To Override DML Statements with Triggers?

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.

(Continued on next topic...)

  1. What Are Triggers?
  2. What Are the Basic Features of a Trigger?
  3. How To Create a Simple Table to Test Triggers?
  4. How To Create a DML Trigger using CREATE TRIGGER Statements?
  5. How To Test a DML Trigger?
  6. How To List All Triggers in the Database with sys.triggers?
  7. How To Modify Existing Triggers using "ALTER TRIGGER"?
  8. How To Delete Existing Triggers using "DROP TRIGGER"?
  9. How To Get the Definition of a Trigger Back?
  10. How To Disable Triggers using "DISABLE TRIGGER"?
  11. How To Create a Trigger for INSERT Only?
  12. How To See the Event List of an Existing Trigger using sys.trigger_events?
  13. How To Access the Inserted Record of an Event?
  14. How To Access the Deleted Record of an Event?
  15. How To Improve the Trigger to Handle NULL Values?
  16. What Happens to a Trigger with Multiple Affected Rows?
  17. How To Override DML Statements with Triggers?
  18. How To Create a DDL Trigger using "CREATE TRIGGER" Statements?
  19. Can You Roll Back the DDL Statement in a Trigger?
  20. Can You Create a Logon Trigger in SQL Server 2005 Express Edition?

Related Articles:

More...


Other Tutorials/FAQs:

More...


Related Resources:

More...


Selected Jobs:

More...