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.

Rooftop Showdown
The Matrix at MOVIECLIPS.com

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

You 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.

But 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.

--Insert trigger
CREATE TRIGGER t_BillDetail_insert ON BillDetail
FOR INSERT AS
BEGIN
    UPDATE bh SET BillLastModifiedDate = inserted.modifiedDate
    FROM BillHeader bh
    INNER JOIN inserted i
        ON bh.BillID = i.BillID
END

--Update trigger
CREATE TRIGGER t_BillDetail_update ON BillDetail
FOR UPDATE AS
BEGIN
    UPDATE bh SET BillLastModifiedDate = inserted.modifiedDate
    FROM BillHeader bh
    INNER JOIN inserted i
        ON bh.BillID = i.BillID
END

--Delete trigger
CREATE TRIGGER t_BillDetail_delete ON BillDetail
FOR DELETE AS
BEGIN
    UPDATE bh SET BillLastModifiedDate = GETDATE()
    FROM BillHeader bh
    INNER JOIN deleted d
        ON bh.BillID = d.BillID
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.

--Instead of update trigger
CREATE TRIGGER t_BillHeader_InsteadOfUpdate ON BillHeader
INSTEAD OF UPDATE AS
BEGIN
    UPDATE bh SET
          bh.CustomerID = i.CustomerID
        , bh.BillDueDate = i.BillDueDate
        , bh.BillAmount = i.BillAmount
        , bh.BillLastModifiedDate = GETDATE()
    FROM BillHeader bh
    INNER JOIN inserted i
        on bh.BillID
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 Cancel reply