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