Create and alter DML triggers
The "Create and Alter DML Triggers" module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises: Implementing DML Triggers, INSTEAD OF Trigger, Creating a Trigger to Handle Multiple Records.
The Create and Alter DML Triggers module provides you with the instruction and server hardware to develop your hands on skills in the defined topics. This module includes the following exercises:
- Implementing DML Triggers
- INSTEAD OF Trigger
- Creating a Trigger to Handle Multiple Records
Lab time: It will take approximately 40 minutes to complete this lab.
Exercise 1 - Implementing DML Triggers
In this exercise, you will learn the following:
- Demonstrating the effect of triggers on inserted and deleted tables
- Creating nested triggers
- Using the UPDATE function in the definition of a trigger
- Creating INSTEAD OF triggers
- Creating AFTER triggers
- Creating a trigger to handle multiple records in a session
Exercise 2 - INSTEAD OF Trigger
An INSTEAD OF trigger causes an insert, update, or delete operations on a table to be cancelled by discarding the respective SQL command submitted to SQL Server. In other words, when you create an INSTEAD OF trigger on a table for a DML operation, the SQL server executes the code that you provide inside the INSTEAD OF trigger instead of the actual DML statement.
In this example, you will create an INSTEAD OF trigger on the view HumanResources.vEmployee for update transactions. The trigger prevents the users from updating the HumanResources_vEmployee view, prints a message, and fetches the record from the view that was attempted to update.
Exercise 3 - Creating a Trigger to Handle Multiple Records
Your SQL statement that causes a DML trigger can affect a single row or multiple rows. For example, an update transaction can affect multiple rows in a table. Likewise, there are chances that more than one record is inserted in a single insert statement. Therefore, the code in your DML trigger must operate accordingly. The AFTER trigger that you created in the previous example handles both single row and multiple row in an insert or update transactions. In this example, let us see one more case where a trigger updates a column in a table by calculating the running total of that column when a related table is updated.
IT & Cybersecurity certification hands on practice labs and practice exams for certifications and skill development.
See the full benefits of our immersive learning experience with interactive courses and guided career paths.