T/SQL - Database Trigger - Part Four

In this article, we will learn about Database Triggers in SQL Server. We also will learn the concept of Magic Tables in SQL Server. This is the fourth part of T/SQL series. You can check the previous articles of this series from these links.

Introduction

Database Trigger is a special type of procedure that is used to provide a restriction on the tables and database when an SQL command is executed.

Type of SQL Server Triggers,
  1. DML Triggers
  2. DDL Triggers 

DML Triggers

DML Triggers execute when the user tries to change the data through manipulation language events. Those are insert, update, and delete stints on the table.

DML Triggers can be used to enforce the business rules and data integrity. With the help of  DML Triggers, we can enforce integrity which cannot be done with constraints.

Syntax

Create Trigger<Trigger Name> on <Table Name>
After /For <Insert/Update/Delete>
As
Begin
<Trigger Body/SQL statement>
End

Note

  1. Triggers are parameter-less objects.
  2. There is no syntax to call in a trigger object because whenever the user executes the DML operations, then a related trigger is invoked automatically.

Example

Create a trigger which will restrict the insert operation on City Table.

  1. create trigger Tri_cities on city  
  2. after insert  
  3. as  
  4. begin  
  5. Print 'Cannot Perform insert Operation in City Table'  
  6. rollback transaction  
  7. end  
Now, let us insert some values in the table.
  1. insert into city values('David','Bangalore','JayaNagar')  

Result

DML Triggers
 
Now, we cannot perform the DML operation in the City Table because the Insert operation is restricted by the Trigger.

Syntax to Drop a DML Trigger

Drop Trigger <Trigger Name>

DDL Triggers

DDL Triggers are used to restrict the DDL Operation like Create, After, and Drop commands.

These triggers are working on a particular database.

Syntax

Create trigger <Trigger name> on database
After/for(create table,alter table,drop table>)
As
Begin
<Trigger body/Query>
End

Example

Create a trigger to restrict creating a table in a database.
  1. create trigger  tri_user on database   
  2. after create_table  
  3. as  
  4. begin  
  5. Print 'Create ARE NOT ALLOWED'  
  6. rollback transaction  
  7. end  

Now, create a table in this database and see.

  1. create table t11(id int,name varchar(50))  

DDL Triggers 

Syntax to drop DDL Trigger 

Drop trigger<TriggerName>

Magic Table in SQL Server

SQL Server allows us to define Magic Table. Magic Tables are invisible tables or virtual tables. We can see them only with the help of triggers in SQL Server. Magic Tables are those tables which allow you to hold inserted, deleted, and updated values during Insert, Delete, and Update DML operation on a table in SQL Server.

Basically, there are two types of Magic Tables in SQL Server - Inserted and Deleted. Magic Table updates can be performed with the help of these two.

Inserted Magic Tables

When we insert the values into the tables, then those values can be seen in the Inserted Magic Table.

Example

  1. create trigger t_com on company  
  2. for insert   
  3. as  
  4. begin  
  5. select * from inserted  
  6. end  

Now, insert some value in the table.

  1. insert into company values(106,'asa',2500)  

Result

Magic Table in SQL Server
 
Deleted Magic Table

When we delete the data from the Table, the deleted data can see in the Deleted Magic Table.

Example
  1. create trigger Tr_delete on company  
  2. for delete   
  3. as  
  4. begin  
  5. select  * from deleted  
  6. end  

Now, delete a value from the table.

  1. delete from company where Companyid=106  

Result

Magic Table in SQL Server

Update the values in Magic Tables

When we updated the values in the Table, then the old value can be seen in the Deleted Magic Table and a new value can be seen in the Inserted Magic Table.

  1. create trigger tr_update on company  
  2. for update  
  3. as  
  4. begin  
  5. select * from inserted  
  6. select * from deleted  
  7. end  
Now, we need to update a value in the table.
  1. update company set CompanyName='ssTechvision' where Companyid=105   
Result

Deleted Magic Table 

Some important queries for triggers - 

Disable a Trigger in SQL Server

  1. disable trigger tri_user on database  

Enable a Trigger in SQL Server

  1. enable trigger tri_user on database  

We can find all the Triggers in a Database by using:

  1. SELECT * FROM sys.triggers  

Result

Enable and Disable Trigger in SQL Server
 
Summary

In this article, we learned about the Triggers in SQL Server and Magic Tables with some examples.