SQL Server Trigger and its types

Trigger Introduction

Triggers are special type of stored procedures which are automatically executed when some event occurs on a database object. They are used to automate database processes and to enforce data integrity rules on database objects.

Types of Triggers

There are three types of triggers in SQL.

• DML triggers
• DDL triggers
• Logon triggers

DML trigger is fired when some data manipulation operations such as inserting rows, deleting rows or updating rows occur in table. In other word, when an action query is executed on a table then after the action query or instead of the query, DML trigger is fired. We can use trigger with view also. View is a virtual table. We will separately see how to use trigger with views. Insert, Delete and Update are event names. Trigger code is event handler.

DDL trigger is fired when create, alter or drop statement is used upon a database object such as table, stored procedure etc. The trigger can fire after the data definition operation.

Syntax of DML Trigger

CREATE TRIGGER <TriggerName>

ON <ObjectName>AFTER | INSTEAD OF <Insert|Delete|Update>
AS

BEGIN
 --Business Logic

END

Syntax - Points to Remember

• Use CREATE TRIGGER statement to create a new trigger object.
• The trigger object name should begin with TR prefix.
• Trigger is always created on a database object e.g. table or view. Use ON clause to refer the target object.
• After the ON clause, we write the type of trigger e.g. after trigger or after delete or after update etc.
• The header and body of trigger are separated by AS keyword.
• The header of trigger contains the name of trigger, target object and type of trigger.
• The body of trigger begins with BEGIN END block which contains the business logic of trigger.

We find that trigger can fire after the occurrence of action query or instead of the query. On this basis, triggers are divided into two types.

• After triggers
• Instead of triggers

Table for DML Trigger

We create employees table and then we will insert some records into it. When row will be inserted into the table, trigger will fire.

CREATE TABLE Employees(
            Id int NULL,
            first_name varchar(10) NULL,
            last_name varchar(10) NULL,
            salary int NULL
)

After Insert DML Trigger

We will create trigger on Employees table so that if row is inserted into this table, the trigger will fire.

Create trigger tr_Employees_Insert
On Employees
After insert
As
Begin
     Print 'Data is inserted into Employees table'
End

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (1, 'Raj','Kumar', 20000)

We find that trigger is fired.

After Delete DML Trigger

Now we create another trigger which will fire when a row is deleted from the table.

Create trigger tr_Employees_Delete
On Employees
After delete
As
Begin
     Print 'Row is deleted from Employees table'
End

Now delete the row inserted into the Employees table.

After Update DML Trigger

Now we create another trigger which will fire when a row is updated into table.

create trigger tr_Employees_Update
on Employees
after update
as
begin
     Print 'Row is updated into Employees table'
end

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (1, 'Raj','Kumar', 20000)

Now update the row as given below.

Update Employees Set first_name = 'Rajesh', last_name='Gupta'
Where Id=1

We get the following message.

Till now we have seen that how trigger is fired when a row is inserted, deleted and updated.

Magic Tables in Trigger

Now we will learn about magic tables which are used when trigger fires. There are following built-in magic tables.

1. Inserted
2. Deleted

The inserted magic table contains a row to hold the current row that is being inserted into the table. The data is first inserted into this magic table and then inserted into target table.

The deleted magic table contains a row to hold the current row that is being deleted from the table. The data is first inserted into this magic table and then deleted from the target table.

The data is first of all inserted into Inserted table when a row is inserted into table. We will verify this by using following example. We will modify the existing trigger for this.

Examples of Magic tables in DML Triggers

alter trigger tr_Employees_Insert
on Employees
after insert
as
begin
     Select i.Id, i.first_name, i.last_name, i.salary
     from inserted i
end

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (2, 'Krishna','Rao', 28000)

 We find the following message.

Remark: Inserted is a special table which holds the latest row inserted into table. It always holds a single row.

We can even store its data into variables as shown below.

alter trigger tr_Employees_Insert
on Employees
after insert
as
begin
     declare @Empid int, @fname varchar(10), @lname varchar(10), @salary int;
     Select @Empid = i.Id, @fname= i.first_name, @lname=i.last_name, @salary=i.salary
     from inserted i
     print  @fname + ' '+@lname + ' has salary equal to ' + cast(@salary as varchar(10))
end

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (3, 'Rina','Ray', 58000)

We find the following message.

Similarly we can use variables, inserted and deleted magic tables to update table.

alter trigger tr_Employees_Update
on Employees
after update
as
begin
	declare @Empid int, @fname varchar(10), @lname varchar(10), @salary int;
	Select @Empid = d.Id, @fname= d.first_name, @lname=d.last_name, @salary=d.salary
	from deleted d
	print  'OLD '+ @fname + ' '+@lname + ' has salary equal to ' + cast(@salary as varchar(10))
	Select @Empid = i.Id, @fname= i.first_name, @lname=i.last_name, @salary=i.salary
	from inserted i
	print 'NEW '+  @fname + ' '+@lname + ' has salary equal to ' + cast(@salary as varchar(10))
end

Update a row into Employees table by running the below Update statement.

Update Employees
Set first_name='Bharat', last_name='Bhushan', salary=88888
Where id=1

We find the following message.

We can look at the trigger definition using system stored procedure.

Drop a trigger

A trigger is a database object which can be dropped using drop trigger statement. The syntax is as given below.

Drop trigger trigger_name

Types of Instead of DML Triggers

There are three types of instead of triggers which are as follows.

• Instead of Insert Trigger
• Instead of Delete Trigger
• Instead of Update Trigger

Instead of trigger implies that instead of an action query (e.g. insert, delete and update) the trigger will execute.

DML Trigger Example Instead of Insert

Let Instead of trigger is applied on a table. If a row is inserted into the table then insertion will fail but the trigger will run instead of the insert statement.

create trigger tr_Employees_Instead_Of_Insert
on Employees
instead of insert
as
begin 
   Print 'Insertion failed into Employees table'
end

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (4, 'Divya','Bharti', 28500)

Check if the row is inserted or not. We find that row is not inserted into Employees table.

DML Trigger Example of Instead of Delete

Suppose that Instead of trigger is applied on a table. If a row is deleted from the table then deletion will fail but the trigger will run instead of the delete statement.

create trigger tr_Employees_Instead_Of_Delete
on Employees
instead of delete
as
begin
     Print 'Deletion failed from Employees table'
end

Delete a row from Employees table by running the below Insert statement.

Delete Employees where Id=1

Check if the row is deleted or not. We find that row is not deleted from Employees table.

DML Trigger Example of Instead of Update

Let Instead of trigger is applied on a table. If a row is updated into the table then update will fail but the trigger will run instead of the update statement.

create trigger tr_Employees_Instead_Of_Update
on Employees
instead of update
as
begin
     Print 'Update failed for Employees table'
end

Update a row of Employees table by running the below Insert statement.

Update Employees
SET first_name='Gaurav', last_name='Yadav'
where Id=1

Check if the row is updated or not. We find that row is not updated in Employees table.

Inserted and Deleted magic tables can be used in case of instead of triggers to know what data was attempted to insert or what row was attempted to delete. Look a simple example in this regard.

alter trigger tr_Employees_Instead_Of_Insert
on Employees
instead of insert
as
begin
     Select * from inserted
end

Insert a row into Employees table by running the below Insert statement.

Insert Employees Values (4, 'Divya','Bharti', 28500)

The data attempted to insert is displayed in the message.

Example of Encryption of triggers

A trigger can be encrypted by using WITH ENCRYPTION.

alter trigger tr_Employees_Instead_Of_Insert
on Employees
WITH Encryption
instead of insert
as
begin
     Select * from inserted
end

Run the following command

sp_helptext tr_Employees_Instead_Of_Insert

We get the following message.

The text for object 'tr_Employees_Instead_Of_Insert' is encrypted.

Where are triggers stored in database?

A trigger created upon a table is stored in Triggers folder in the table as shown below.

Trigger with View

In the previous post we have learnt about triggers with table. We can use trigger with view also. A view can be based on a single table or more than one table.

• Single table based view
• Multiple tables based view

In case of single table based view, the trigger will affect the table of view indirectly. It means that if row is inserted into view then row will be inserted into table of the view. Similarly row is deleted from view then row will be deleted from the table of the view.

In case of multiple table based view, the trigger will affect rows of multiple tables which are joined to create view. It means that if row is inserted into view then row will be inserted into joined tables of the view. Similarly if row is deleted from view then rows will be deleted from multiple tables of the view.

Remark: We should use instead of triggers with view so that rows of the view is not affected but its associated tables are affected. The view will be updated as per its base tables.

Example of Single Table based View

Let we have employees table with columns such as id, first name, last name, salary, DOB, department id. We create a view from this employees table.

use AppliedkDb
go

create table Employees
(
id int,
first_name varchar(10),
last_name varchar(10),
salary int,
department_id int
)

Now we create a view from Employees table.

Create view vwEmployees
AS
SELECT Id, first_name, last_name, salary, Department_id
from Employees

Now we create instead of DML trigger upon the vwEmployees view.

use AppliedkDb
go
create trigger tr_employees_OnView
on [dbo].[vwEmployees]
instead of insert
as
Begin
     declare @id int, @fn varchar(10), @ln varchar(10), @sal int, @d int;
     select @id= id, @fn=first_name, @ln=last_name, @sal= salary, @d= Department_id
     from inserted;
     insert into Employees(Id, first_name, last_name, salary, department_id)
     values( @id, @fn, @ln, @sal, @d );
End

Now we insert row into view.

Insert vwEmployees Values (1, 'Divya','Bharti', 28500, 1)
Insert vwEmployees Values (2, 'Ramesh','Gupta', 55500, 1)
Insert vwEmployees Values (3, 'Divya','Rao', 45500, 2)
Insert vwEmployees Values (4, 'Vicky','Gupta', 55500, 1)
Insert vwEmployees Values (5, 'Danny','Rao', 30500, 2)
Insert vwEmployees Values (6, 'Mahesh','Gupta', 70500, 1)

Example of Multiple Tables based View

Create table Department
(
Depart_Id int,
Department_Name varchar(10)
)
insert into Department values(1, 'HR')
insert into Department values(2, 'IT')
insert into Department values(3, 'Sales')

Now create view by joining Department and Employees tables.

Create view vwEmpDepartments
As
select e.Id, e.first_name, e.last_name, d.Depart_Id
from employees e
inner join Department d
on e.department_id = d.Depart_Id 

Since we have created view based on multiple tables, we will now use following instead of triggers on this view.

1. Instead of Insert trigger
2. Instead of Delete trigger
3. Instead of Update trigger

Instead of Insert trigger

Instead of Delete trigger

Instead of Update trigger

DDL Triggers

DDL trigger is fired when create, alter or drop statement is used upon a database object such as table, stored procedure etc. The trigger can fire after the data definition operation. The scope of DDL trigger can be limited either to all objects of a database or to all objects of server. It means that in later case, all databases and their objects will be within the scope of trigger.

DDL Trigger Syntax

CREATE TRIGGER 
ON {ALL Server | Database}
 AFTER | FOR {Event Type}
AS
BEGIN
    --Business Logic
END

Event Type can be CREATE_TABLE, ALTER_ TABLE, DROP_ TABLE etc.

Applications of DDL trigger

We can apply DDL trigger to prevent certain changes into database schema.

Server Scoped DDL Triggers In SQL Server

In SQL Server, a Server Scoped DDL (Data Definition Language) Trigger is a special type of trigger that responds to events related to changes in the database structure or schema. DDL triggers are used to monitor and respond to actions such as creating, altering, or dropping database objects like tables, views, and stored procedures.

Here are the key points about Server Scoped DDL Triggers:

Scope:

Server Scoped DDL Triggers operate at the server level, meaning they can monitor and respond to DDL events that occur across all databases on the SQL Server instance.

Events:

These triggers can be configured to respond to specific DDL events, such as CREATE, ALTER, or DROP statements related to objects like tables, views, stored procedures, and more.

Common Use Cases:

1. Auditing: Tracking changes to the database schema for security or compliance purposes.
2. Enforcing Policies: Enforcing specific rules or policies when certain types of objects are created, altered, or dropped
3. Preventing Undesired Changes: Preventing accidental or unauthorized changes to critical database objects.

Syntax:

The syntax for creating a Server Scoped DDL Trigger is as follows:

CREATE TRIGGER trigger_name
ON ALL SERVER
AFTER DDL_EVENT
AS
-- Trigger body, including T-SQL statements to be executed.

Here, trigger_name is the name you assign to the trigger, and DDL_EVENT is the specific event or events you want to capture.

Example:

Here's a simple example of a DDL trigger that logs information when a new table is created:

CREATE TRIGGER LogTableCreation
ON ALL SERVER
AFTER CREATE_TABLE
AS
BEGIN
    -- Log information about the table creation
    PRINT 'New table created. Log the event in the audit table.';
END;

Considerations:

Server Scoped DDL Triggers can have a significant impact on server performance, especially if the trigger's logic is complex or involves resource-intensive operations. Care should be taken to ensure that triggers do not introduce performance bottlenecks.

Management:

Server Scoped DDL Triggers can be managed using SQL Server Management Studio (SSMS) or through T-SQL statements to enable, disable, or drop triggers.

It's important to design and use DDL triggers judiciously, considering the specific requirements of your database environment and the potential impact on performance. Additionally, thorough testing and monitoring are recommended when implementing triggers to ensure they behave as expected.