Create and alter DML triggers

Practice Labs Module
Time
50 minutes
Difficulty
Intermediate

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.

Join over 3 million cybersecurity professionals advancing their career
Sign up with
or

Already have an account? Sign In »

Overview

Introduction

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.

Learning Partner
Comprehensive Learning

See the full benefits of our immersive learning experience with interactive courses and guided career paths.