Triggers in SQL Server

Watch this video on SQL Server Triggers for Beginners

What is a Trigger in SQL Server?

Triggers are database objects similar to stored procedures that run automatically when database events occur. A Trigger can be set off by a variety of different actions, such as adding or removing rows from a table, logging into a database server instance, updating a table column, creating, modifying, or deleting a table, etc. They are also used to maintain data integrity. Manage server operations, monitor servers, and apply business logic or business rules.

Purpose of Trigger in SQL Server

Triggers are used to automate the code execution whenever an event takes place. Triggers are the best option if you need a specific piece of code that will always be implemented in response to an event. Specifically, they ensure that the code will be implemented or that the event that prompted the trigger will fail.

Triggers are used for various purposes, they are in the following:

• Perform additional checks on the impacted table during insert, update, or delete operations.
• They enable the encoding of complicated default values that default constraints cannot handle.
• They allow controlling what happens when someone inserts, updates, or deletes data from a view that accesses two or more tables.
• Using triggers, users can determine aggregated columns in a table.

Advantages and disadvantages of Trigger in SQL Server

Triggers are useful because they help maintain data integrity in database tables, but they are difficult to maintain because they are difficult to find. Because they execute all insert, update, or delete commands from the table, they can slow down performance. So, there are various benefits and drawbacks of Trigger which we have discussed below.

Advantages of Trigger in SQL Server:

• Triggers in SQL are easy to code.
• Triggers in SQL allow you to create basic controls.
• Triggers can be used to call stored procedures and functions.
• Triggers are useful when you need to check input data or update data in batches instead of rows.
• You can use Triggers to ensure referential integrity in a database. Unfortunately, SQL Server doesn’t allow you to set up constraints between objects in different databases, but you can use Triggers to simulate the behavior of constraints.
• Triggers are useful for firing specific events whenever data is added, updated, or deleted. This happens when working with complex default column values or changing data from other tables.
• You can use external code as Triggers with CLR Triggers. This Trigger type refers to a collection method written using Triggers in .NET.
• Up to 32 levels of Triggers can be nested. Triggers are considered nested when performing an action and activating other Triggers.
• The Trigger includes repetition. Triggers are recursive if a Trigger on a table performs an action that Triggers another instance of the underlying table. This is useful when working with self-referential relationships.

Disadvantages of Trigger in SQL Server:

• Setting recursive triggers is more difficult than setting nested triggers.
• If you use triggers to enforce referential integrity, be aware that users with ALTER access or users who have seen the trigger can disable them. To avoid this, you may need to check user permissions.
• Triggers should be well documented.
• Triggers add redundancy to DML statements.
• Many nested triggers can be very difficult to troubleshoot and solve problems that require development time and resources.
• Triggers for BULK INSERTS are not fired unless you include the FIRE_TRIGGERS option in the bulk insert statement.

Classes of SQL Server Triggers

In SQL Server, there are two categories of Triggers:

• DDL (Data Definition Language)– This category of Triggers are activated by structure-changing events such as the creation, modification, or drop of a table, as well as by some server-related events, such as security updates or statistic update event.
• DML (Data Modification Language)– The majority of Triggers fall under this category. In this instance, a data change statement which could be an insert, update, or delete statement on a table or a view is the Triggering event.

DDL Triggers in SQL Server

The DDL Trigger executes an event in Create, Alter or Drop commands in a database.

Example-

• Create an insert Trigger for a table named “Sales”

//Insert Trigger Creation

create trigger trigger_insert on Sales
after insert
as
begin
PRINT 'YOU HAVE INSERTED A ROW'
end

//To view the Trigger activation

insert into Sales values (9,4, '04-11-2012',15670.50, 'FALSE')

• Create an update Trigger in the table named “Stores”

//Update Trigger Creation

create trigger trigger_update on Stores
after update
as
begin
PRINT 'YOUR RECORD HAS BEEN UPDATED!'
end
SELECT * FROM [dbo].[Stores]

//To view the Trigger activation

UPDATE Stores set Type='A' where Store =1

• Create a delete Trigger in the Features table.

//Delete Trigger Creation

create trigger trigger_delete on Features
after delete
as
begin
PRINT 'YOUR RECORD HAS BEEN DELETED!'
end
select * from [dbo].[Features]

//To view the Trigger activation

delete Features where Temperature='38.51'

DML Triggers in SQL Server

The DML Trigger executes an event in Insert, Update or Delete commands in a table.

• Create a create Trigger for your database.

//Create Trigger Creation

create trigger create_trigger
on database
after create_table
as
begin
Print 'Table Created'
End

//To view the Trigger activation

create table sample_table (id int)

• Alter a create Trigger from your database to drop trigger.

//Altering Create Trigger to Drop Trigger

alter trigger create_trigger
on database
after drop_table
as
begin
Print 'Table Dropped'
End

//To view the Trigger activation

drop table sample_table

• Create a alter Trigger for your database.

//Alter Trigger Creation

create trigger alter_trigger
on database
after alter_table
as
begin
Print 'Table Altered'
end

//To view the Trigger activation

alter table sample_table
alter column id varchar(5)