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
 3: FOR INSERT AS
 4: BEGIN
 5:     UPDATE bh SET BillLastModifiedDate = inserted.modifiedDate
 6:     FROM BillHeader bh
 7:     INNER JOIN inserted i
 8:         ON bh.BillID = i.BillID
 9: END
 10:
 11: --Update trigger
 12: CREATE TRIGGER t_BillDetail_update ON BillDetail
 13: FOR UPDATE AS
 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
 20:
 21: --Delete trigger
 22: CREATE TRIGGER t_BillDetail_delete ON BillDetail
 23: FOR DELETE AS
 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
 3: INSTEAD OF UPDATE AS
 4: BEGIN
 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!

Leave a Reply

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