1. INSTEAD OF triggers (DML)
In contrast with AFTER triggers, INSTEAD OF triggers can only be used for DML statements (not DDL).
This is because they were designed to work with DML statements: INSERT, UPDATE, and DELETE.
2. Definition and properties
An INSTEAD OF trigger will perform an additional set of actions when fired, in place of the event that fired the trigger. That event is not run when using an INSTEAD OF trigger. This is the main difference between the two trigger types.
In terms of the DML statements that are able to fire an INSTEAD OF trigger, we have the same list: INSERT, UPDATE, and DELETE.
3. INSTEAD OF trigger prerequisites
As before, let's explore the steps we would perform to create an `INSTEAD OF` trigger.
For this example, we will use the "Orders" table that holds details of the orders placed by the Fresh Fruit Delivery company's customers.
4. INSTEAD OF trigger prerequisites
Following the same steps for our analysis, we know that our target table is the "Orders" table.
The trigger we will create should prevent updates to existing entries in this table. This will ensure that placed orders cannot be modified;this is a rule enforced by the company.
This means that the trigger will fire as a response to UPDATE statements.
The chosen name for the trigger is "PreventOrdersUpdate".
Having an informative name is important when creating triggers. It's good if the name can set some expectations about what the trigger is intended to achieve.
5. INSTEAD OF trigger definition
Now that we have gathered all the needed details, we can start creating the trigger.
The trigger definition is quite straightforward: do not allow row updates on the "Orders" table.
You should already be familiar with the CREATE TRIGGER syntax from past examples. Here we use "PreventOrdersUpdate" for the trigger name.
As we know from the preceding analysis, the trigger will be attached to the "Orders" table and it will be executed when UPDATE statements are run.
We also want to inform the end user about the rule we set in place through the trigger, so we're going to use the RAISERROR syntax to throw an error message as output. The numbers after the error message represent the "severity" and the "state" of the thrown error.
The severity of the error in this example is 16. This is the most common value; it means we are throwing a medium-level error.
The state parameter is used to identify the error statement in the SQL code if it is used multiple times.
We will be using the value 1 because the error is used only one time and can be easily identified in the SQL code.
A further explanation of the `RAISERROR` function is outside the scope of this course.
6. Let's practice!
Don't you think it would be a good idea to actually create the trigger and see it in practice? Let's do it!