Can you explain triggers?

    Requires Free Membership to View

A trigger is a code object that executes when a table is modified by a SQL INSERT, UPDATE, or DELETE statement. In SQL Server 2000 and earlier versions, triggers are written in Transact-SQL. In the next version of SQL Server, known as Yukon or SQL Server 2005, triggers may also be written in any .NET language such as C# or VB.NET.

Each trigger is associated with one table and with one or more of the three SQL Data Modification Language (DML) statements mentioned above. The typical trigger runs after an INSERT, UPDATE, or DELETE statement on the table that it's associated with.

Some of the common uses for triggers are:

  • Enforce referential integrity rules
  • Enforce other rules on data in a table
  • Synchronize information in another table when a table is updated
  • Initiate some action external to SQL Server when an update occurs
  • Prevent some types of updates, for example, DELETEs

The rules enforcement aspect of triggers has become less urgent since SQL Server 7 and above can enforce referential integrity and check constraints. However, when the rules involve interactions between columns, a trigger may be the most effective method to enforce it. The other uses of triggers remain just as common as before.

Here's a simple example trigger for the ROYSCHED's table in the sample pubs database: The trigger enforces a rule that the lorange column must be no more than 10 percent less than the hirange column. First take a look at the trigger:

 USE PUBS GO CREATE TRIGGER ROYSCHED_UDPATE_RULES ON ROYSCHED AFTER INSERT, UPDATE AS IF UPDATE(lorange) or UPDATE(hirange) BEGIN IF 0 != (SELECT SUM(CASE WHEN lorange < hirange * .9 THEN 1 ELSE 0 END) FROM INSERTED) BEGIN RAISERROR ('lorange must be within 10 PERCENT of hirange',12,1) IF @@TRANCOUNT > 0 ROLLBACK TRAN END END

For update statements triggers have two special tables, INSERTED and DELETED, that contain the rows of the update after and before the update. INSERT statements only have the INSERTED table and DELETE statements only have the DELETED table. In the trigger above, the rule is enforced by examining all the rows of the INSERTED table and if any of them violate the rule, an error is raised and the transaction is rolled back.

Let's try updating some rows in ROYSCHED and see what happens when the rules are violated in the first statement and when they are not violated in the second statement:

 -- This update violates the trigger's range limitation UPDATE ROYSCHED SET LORANGE = HIRANGE * .80 WHERE TITLE_ID LIKE 'BU%' (Result) Server: Msg 50000, Level 12, State 1, Procedure ROYSCHED_UDPATE_RULES, Line 12 lorange must be within 10 PERCENT of hirange -- This update obeys the trigger's range limitation UPDATE ROYSCHED SET LORANGE = HIRANGE * .95 WHERE TITLE_ID LIKE 'BU%' (Result) (26 row(s) affected)

Triggers can also be disabled and later re-enabled with the ALTER TABLE statement. Disabling a trigger is a frequent cause of confusion. A disabled trigger is like no trigger at all, so if you're not expecting a trigger to be disabled, you may be surprised by the results of your INSERTS, UPDATES, and DELETES. For a convenient user-defined function, udf_Trigger_StatusTAB, that shows which triggers are disabled, take a look at Volume 2 #7 of my newsletter.

Triggers are one of the code objects that allow SQL Server databases to go beyond being just a place to store data and turn them into intelligent applications. They should be part of every intermediate to advanced SQL programmers repertoire.

This was first published in March 2005

There are Comments. Add yours.

 
TIP: Want to include a code block in your comment? Use <pre> or <code> tags around the desired text. Ex: <code>insert code</code>

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
Sort by: OldestNewest

Forgot Password?

No problem! Submit your e-mail address below. We'll send you an email containing your password.

Your password has been sent to: