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.

Want to get certified in SQL Server? Learn from our SQL expert with Intellipaat’s SQL Certification.

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.

Get 100% Hike!

Master Most in Demand Skills Now !

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.

Wish to crack SQL interviews? Intellipaat’s Top SQL Interview Questions are meant only for you!

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.

Career Transition

Got Job With Salary Hike | Process Developer to Sr. Software Engineer Career Transition
Got Multiple Job Offers with 100% Salary Hike | Best SQL Course | Intellipaat Career Transition
How To Switch Career From A Support Role To Oracle DBA Engineer | Got Job With 80% Salary Hike
How To Become A Database Expert From A Non Tech Background | Got Job With Salary Hike | Intellipaat
Got Job as Cloud DBA Just After completion of the Course | Intellipaat Career Transition
SQL Developer to Software Engineer Career Transition | Got Job with 120% Salary Hike | Intellipaat

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.

Enroll now in SQL training in Bangalore to learn more about SQL concepts.

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')

view the Trigger activation

  • 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

Trigger activation

Preparing for the SQL interviews? Check out these SQL Server Interview Questions.

  • 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'

Temperature

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)
  • Trigger activation To ViewAlter 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

view the Trigger activation 2

  • 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)

Table alter

Drop any of your queries in our SQL Server Community and start a discussion.

Course Schedule

Name Date Details
SQL Training 30 Mar 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 06 Apr 2024(Sat-Sun) Weekend Batch
View Details
SQL Training 13 Apr 2024(Sat-Sun) Weekend Batch
View Details