Triggers in SQL Server

Triggers in SQL Server are mainly used for implementing complex business rules. A trigger is a named T-SQL block executed implicitly.

Triggers in SQL Server are created

  1. To implement complex business rules and validations
  2. To provide security
  3. To do auditing

Business rules are of 2 types

  1. Declarative rules – Implemented with the help of Integrity constraints. for instance minimum salary of employee should not greater than 10,000.
  2. Procedural rules – Implemented with the help of triggers. Example for this is transaction has to be happening in banks between 10 to 5 pm from Monday to Friday, Saturday from 10am to 1pm and on Sunday transactions should not happen.

How to find Triggers in SQL Server?

Open the SSMS -> Expand the databases -> choose your database -> logship -> expand it -> under programmability -> database triggers

Triggers in SQL Server

Purpose of Triggers in SQL Server:

Check Constraints and Triggers both will validate the data during DML operations.

Use Check constraint when the requirement is simple else use triggers.

Triggers do not support programming constructs like if else, loop etc.

Need for the Triggers in SQL Server:

  1. Validations (Pre processing)
  2. Post processing work (jobs)

Need :

Pre Processing ( Validations):

  1. Complex Business Rules
  2. Rules involving columns from more than one table
  3. Rules involving old and new data in the same table.

Post Processing(Job):

DML (Insert, Update, Delete) operations in same table or different table.

Types of Triggers in SQL Server:

DDL Triggers

DML Trigger

1. After or For

2. Instead of

types of triggers in SQL Server

Question) When does a trigger fire?

Answer) During DML operations (Insert, Update, Delete)

Can I call a trigger explicitly?

No

Note :- Trigger can not be created on a column, it can be created on table.

Truncate does not invoke trigger.

Syntax to create a trigger:

create trigger <trigger name>
on <table name>
<type><insert, update, delete>
as
begin
code
end
go

Magic Tables:

Magic Table created during trigger creation

During trigger creation 2 tables are created

  1. Inserted table :- Temporary table created by SQL Server. The record, user is trying to insert is copied to inserted table
  2. Deleted table:- The record, user is trying to delete is copied to deleted table. The record, user is trying to update is copied to both the tables.

After trigger:

Can be created only on tables

Multiple insert, update, delete triggers.

Instead of Trigger:

Can be created on tables and views.

Only one instead of trigger of a given type ( insert, update, delete)

This trigger gets fired instead of transaction.

Difference between stored procedure and tiggers?

Stored Procedure:

  1. Procedures invoke explicitly
  2. Procedure can be created without a table.
  3. Procedures accepts parameters
  4. Procedures can return values
  5. Procedures can be invoked from frond end
  6. create procedure for performing DML operations

Triggers:

  1. Triggers invoked implicitly.
  2. Triggers can not be created without a table.
  3. Triggers will not accept parameters
  4. Triggers can not return values
  5. Triggers can not be invoked from front end
  6. Create trigger to control DML operations.
Difference between stored procedure and trigger

Instead of triggers are created to update complex views.

Normally simple views are updatable, complex views are not updatable but with the help of instead of triggers we can update complex views.

Leave a Comment

Your email address will not be published. Required fields are marked *