70-433: So, When Do You Want To Use Triggers?

Ok, let’s cover another topic you’ll need to know in order to pass the 70-433.  You’re going to need to be familiar with what triggers can do.  When you get ready for the 70-451, you’ll need to know more about when triggers should be used.  I wanted to use the scene from Raiders of the Lost Ark.  You know the scene.  A big burly swordsman comes to face Indy, and he simply pulls his pistol and shoots.

Yeah, but this works too.  Sorry for two Matrix references in a row.


Let’s cover a situation where you need to know what a trigger can do.

triggers1You have two tables in your database.  The first table is BillHeader, this table is the descriptive information about a bill.  This table contains CustomerID, BillAmount, etc.

The BillLastModifiedDate should reflect the last time the BillHeader or the BillDetail table is updated.  Changing the last modified date when updating a record in the BillHeader table is no issue.  That’s what an update statement is for.

triggers2But updating the BillHeader table whenever the BillDetail table is changed is quite different.

If you want to update one table whenever another table is updated (and you’re not talking about a foreign key), you are talking about a trigger.  That’s exactly when you want to use a trigger.

So let’s dive into some knowledge about triggers you’ll need for the 70-433.

There are there are three modifications to the BillDetail table that you’ll need to build triggers to handle in order to keep BillHeader.BillLastModifiedDate properly updated.  So that means you’ll need to construct three CREATE TRIGGER statements, one for each scenario.

 1: --Insert trigger
 2: CREATE TRIGGER t_BillDetail_insert ON BillDetail
 5:     UPDATE bh SET BillLastModifiedDate = inserted.modifiedDate
 6:     FROM BillHeader bh
 7:     INNER JOIN inserted i
 8:         ON bh.BillID = i.BillID
 9: END
 11: --Update trigger
 12: CREATE TRIGGER t_BillDetail_update ON BillDetail
 14: BEGIN
 15:     UPDATE bh SET BillLastModifiedDate = inserted.modifiedDate
 16:     FROM BillHeader bh
 17:     INNER JOIN inserted i
 18:         ON bh.BillID = i.BillID
 19: END
 21: --Delete trigger
 22: CREATE TRIGGER t_BillDetail_delete ON BillDetail
 24: BEGIN
 25:     UPDATE bh SET BillLastModifiedDate = GETDATE()
 26:     FROM BillHeader bh
 27:     INNER JOIN deleted d
 28:         ON bh.BillID = d.BillID
 29: END

These triggers were constructed with the following assumptions.

  • You have a foreign key preventing the user from changing BillDetail.BillID to an invalid value.
  • For the Insert and Update triggers, the modifiedDate will be set properly.  If it weren’t you could change the trigger to grab GETDATE() like the DELETE trigger does.

Now, for the 70-433, you will also want to know the difference between when a FOR or AFTER trigger fires, and when an INSTEAD OF trigger fires.  Just knowing the names should be enough to answer any questions on this first exam.  INSTEAD OF replaces the action, while FOR or AFTER fires immediately after the action, and before the transaction for that action expires.

If, you’re asked to do something the action itself cannot do, you’ll want to use the INSTEAD OF.  For example, let’s say you no longer trust that BillHeader.BillLastModifiedDate is being set from the application.  You want to make sure that it’s always set to the current SQL Server’s time whenever an update happens, then that’s the perfect place to use a trigger.

 1: --Instead of update trigger
 2: CREATE TRIGGER t_BillHeader_InsteadOfUpdate ON BillHeader
 5:     UPDATE bh SET
 6:           bh.CustomerID = i.CustomerID
 7:         , bh.BillDueDate = i.BillDueDate
 8:         , bh.BillAmount = i.BillAmount
 9:         , bh.BillLastModifiedDate = GETDATE()
 10:     FROM BillHeader bh
 11:     INNER JOIN inserted i
 12:         on bh.BillID
 13: END

Notice I’m setting all the other columns in this trigger.  Since you are replacing the action of an insert, you need to explicitly code the whole action that should be taken in place of the update.  In this case, I want to make the changes to the other columns as they would happen normally.

That, in a  nutshell, is what you’ll need to know about triggers for the 70-433.  If you have any questions on your prep…let me know!  I’m here to help!

By Shannon Lowder

Shannon Lowder is the Database Engineer you've been looking for! Look no further for expertise in: Business Analysis to gather the business requirements for the database; Database Architecting to design the logical design of the database; Database Development to actually build the objects needed by the business logic; finally, Database Administration to keep the database running in top form, and making sure there is a disaster recovery plan.

Leave a comment

Your email address will not be published. Required fields are marked *