Creating Triggers

Introduction

The Creating Triggers module provides you with the instructions and devices to develop your hands-on skills in the following topics.

• Working with DDL triggers
• Working with DML triggers
• Working with logon triggers

Lab time: It will take approximately 50 minutes to complete this lab.

Exam Objectives

Five exam objectives are covered in this lab.

• Demonstrate how to create a DDL trigger using Transact-SQL statement
• Demonstrate how to create a DML trigger using Transact-SQL statement
• Demonstrate how to create a logon trigger using Transact-SQL statement
• Demonstrate how to create a DML trigger using the argument AFTER in the triggering SQL statement
• Demonstrate how to create a DML trigger using the argument INSTEAD OF in the triggering SQL statement

Exercise 1 - Working with DDL Triggers

Triggers are specific kind of stored procedures that are executed automatically in response to certain actions or events in the database. A trigger can also be referred to as a database object, attached to a table.

There are basically three types of events occurring in a database. These are as follows:

• Data Definition Language (DDL) events
• Data Manipulation Language (DML) events
• Database operation events

You can create triggers on events associated with database objects. You can create multiple triggers for any specific event. Triggers are used to monitor the changes in data and maintain consistency and accuracy of the modified data.

In SQL Server, you can create DDL triggers using Transact-SQL statements to respond to events, such as CREATE, ALTER, DROP, and so on.

The syntax to create a DDL trigger is as follows:

CREATE [ OR ALTER ] TRIGGER trigger_name

ON { ALL SERVER | DATABASE } [ WITH <ddl_trigger_option> [ ,...n ] ] { FOR | AFTER } { event_type | event_group } [ ,...n ] AS { sql_statement [ ; ] [ ,...n ]

In this exercise, you will learn to create a DDL trigger using Transact-SQL statements.

Exercise 2 - Working with DML Triggers

DML triggers execute automatically when DML events occur on the table or view defined in the trigger. DML events are Transact-SQL statements such as INSERT, UPDATE, DELETE, and so on. These triggering events can be mentioned using the ‘OR’ keyword for relevant execution. DML triggers help to protect the accuracy of the information in the database when subjected to changes. A DML trigger will execute once for every statement that is run on the table, irrespective of the number of rows that are modified in the table.

The syntax to create a DML trigger using Transact-SQL statements is as follows:

CREATE [ OR ALTER ] TRIGGER [ schema_name . ]trigger_name ON { table | view } [ WITH <dml_trigger_option> [ ,...n ] ] { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] } [ WITH APPEND ] [ NOT FOR REPLICATION ] AS { sql_statement [ ; ] [ ,...n ]

In this exercise, you will learn to create a DML trigger using Transact-SQL statements.

Exercise 3 - Working with Logon Triggers

Whenever there is an occurrence of a LOGON event in the database, the logon triggers execute stored procedures. The LOGON event occurs when you, as a database user, establish a session with the database. When you login to the database, after the authentication process, the logon trigger executes. Therefore, logon triggers execute before you establish the session. The SQL Server error logs collect the alert or the error messages that you include in the logon triggers.

Suppose if the login authentication process is unsuccessful, the logon triggers will not execute. You can control and monitor the server sessions using logon triggers. You can also use logon triggers to stop the creation of database user sessions. You must create logon triggers in real-time environment only in unavoidable circumstances.

The syntax to create a logon trigger using Transact-SQL statement is as follows:

CREATE [ OR ALTER ] TRIGGER trigger_name ON ALL SERVER [ WITH <logon_trigger_option> [ ,...n ] ] { FOR| AFTER } LOGON AS { sql_statement [ ; ]

In this exercise, you will learn to create a logon trigger using Transact-SQL statement and execute it.