Use cases for INSTEAD OF triggers (DML)
1. Use cases for INSTEAD OF triggers (DML)
If AFTER triggers are used mostly for auditing and logs, the story is different with INSTEAD OF triggers. We are going to learn the details in this lesson.2. General use of INSTEAD OF triggers
The use cases for these triggers are suggested by their name: INSTEAD OF. These kinds of triggers focus on preventing certain operations from happening in your database. If you can control the permitted statements inside your database and set some rules, you can enforce data integrity through triggers as well.3. Triggers that prevent changes
In this example, updates to the "Products" table are not permitted for regular database users. When a non-administrator runs an UPDATE statement, the trigger will raise an error using the RAISERROR function. An error message will be included to inform the user that they aren't allowed to make any changes to this table. The restriction is applied because the table contains information about stock. An incorrect UPDATE statement could wreak havoc by concealing the real stock numbers.4. Triggers that prevent and notify
In this second example, the trigger doesn't just raise an error message to prevent the deletion of a customer. An alert destined for the database administrator is also sent. When a user tries to remove a customer from the "Customers" table, an email will be sent to the database administrator. The removal action is, of course, denied with an error message. The body of the message is stored in the "EmailBodyText" variable and will include the name of the user who attempted to make the change. After raising the error, the trigger executes the "SendNotification" procedure to send the email alert to the database administrator.5. Triggers with conditional logic
INSTEAD OF triggers should not always be considered as objects that deny operations on the database. They can be used to decide whether or not some operations should succeed. In this example, we create a new trigger on the "Orders" table. It makes no sense for an order to be placed if there is insufficient stock of the product. An INSTEAD OF trigger can check whether there is sufficient stock for an order, through an IF statement. The process used to decide what the trigger will do is called "conditional logic". It gets this name because operations will be performed or not based on logical conditions, like the IF statement. In this example, the IF statement checks for the condition where the order quantity is higher than the existing stock level. If the condition is true, an error will be raised and the order will be denied. If the condition is false, indicating that there is sufficient stock, the initial INSERT operation is executed. The initial INSERT operation can only be executed if stated explicitly in the trigger code.6. Triggers with conditional logic
Here's what the conditional logic used in the trigger looks like. First, the INSERT operation fires the trigger. The trigger then verifies whether there is sufficient stock of the product included in the INSERT operation. If the condition is true, the order is placed by adding a new row in the "Orders" table. If it's false, the trigger throws an error and the INSERT operation is dropped.7. Let's practice!
Ready to practice with some triggers? Let's do it!Create Your Free Account
or
By continuing, you accept our Terms of Use, our Privacy Policy and that your data is stored in the USA.