What is a Trigger? What are the Types Of Triggers? How to Display Information About Events - Part One

Introduction

 
While working with any technology, we need to study its important aspects and features. When we talk about Database Technology, then we should very clear about the basics of the database to learn advanced features such as a stored procedure, functions, views, packages, sequence, synonyms and triggers. In this article, I will explain what is a trigger and the types of triggers.
 
Let’s begin...
 

What is a trigger?

 
A trigger is a special type of stored procedure in database that automatically invokes/runs/fires when an event occurs in the database server. A trigger uses the special table to keep a copy of the row which we have just inserted, deleted or modified.
 

What are the types of triggers?

 
There are three types of triggers in SQL Server.  
  1. DDL Trigger
  2. DML Trigger
  3. Logon Trigger
DDL Trigger
 
DDL trigger runs when DDL events occur in the database. DDL events are CREATE, ALTER and DROP statements.
 
The DDL triggers are useful in the following cases:
  • Record changes in the database schema
  • Prevent some specific changes to the database schema
  • Respond to a change in the database schema
To create a DDL trigger, use the following syntax
  1. CREATE TRIGGER <Trigger_Name>  
  2. ON DATABASE  
  3. FOR CREATE_TABLE,ALTER_TABLE,DROP_TABLE  
  4. AS  
  5. BEGIN  
  6. <Trigger Body>  
  7. END    
We will see it practically, so first, open SQL Server Management Studio, take a new worksheet, and create a new database name, 'DEMOS', shown below:
 
What Is Trigger? What Are The Types Of Triggers? How To Display The Information About Event
 
Suppose you want to capture all the modifications made to the database index so that you can better monitor the performance of the database server which relates to these index changes.
 
First, create a new table named ‘Index_logs’ to log the index changes.
  1. CREATE TABLE Index_logs (  
  2.     log_id INT IDENTITY PRIMARY KEY,  
  3.     event_data XML NOT NULL,  
  4.     changed_by SYSNAME NOT NULL  
  5. );   
Also, create one more new table as ‘customers’ which will create the index.
  1. CREATE table customers(  
  2. Cust_id INT IDENTITY PRIMARY KEY,  
  3. first_name varchar(20),  
  4. last_name varchar(20),  
  5. address_ varchar(20),  
  6. );   
Now ‘Index_logs’ table and ‘customers’ are created, when you refresh 'Databases' in object explorer window, you can see it. Alternatively, you can see it by executing the select command.
 
What Is Trigger? What Are The Types Of Triggers? How To Display The Information About Event
 
Next, create a DDL trigger to track index changes and insert events data into the ‘Index_logs’ table.
  1. CREATE TRIGGER trg_index_changes  
  2. ON DATABASE  
  3. FOR   
  4.     CREATE_INDEX,  
  5.     ALTER_INDEX,   
  6.     DROP_INDEX  
  7. AS  
  8. BEGIN  
  9.     SET NOCOUNT ON;  
  10.    
  11.     INSERT INTO Index_logs (  
  12.         event_data,  
  13.         changed_by  
  14.     )  
  15.     VALUES (  
  16.         EVENTDATA(),  
  17.         USER  
  18.     );  
  19. END;  
In the body of the trigger, we used the EVENTDATA() function that returns the information about server or database events. The function is only available inside DDL or logon trigger.
 
Then, create indexes for the first_name and lasst_name columns of the customers table:
  1. CREATE INDEX nidx_fname ON customers(first_name);  
  2. CREATE INDEX nidx_lname ON customers(last_name);   
After that, write execute the below query to check whether the index creation event was captured by the trigger properly.
  1. SELECT * FROM Index_logs;   
When you execute the above query it shows the below output:
 
What Is Trigger? What Are The Types Of Triggers? How To Display The Information About Event
 
If you click on the cell of the 'event_data' column, you can view XML data of the event as follows:
 
What Is Trigger? What Are The Types Of Triggers? How To Display The Information About Event 
 

Summary

 
In this article, we learned what about a  trigger, the types of trigger, and how to create a DDL trigger and display the information about an event. In the next article, I will explain a DML trigger and how to create one.