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
- To implement complex business rules and validations
- To provide security
- To do auditing
Business rules are of 2 types
- Declarative rules – Implemented with the help of Integrity constraints. for instance minimum salary of employee should not greater than 10,000.
- 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
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:
- Validations (Pre processing)
- Post processing work (jobs)
Pre Processing ( Validations):
- Complex Business Rules
- Rules involving columns from more than one table
- Rules involving old and new data in the same table.
DML (Insert, Update, Delete) operations in same table or different table.
1. After or For
2. Instead of
Question) When does a trigger fire?
Answer) During DML operations (Insert, Update, Delete)
Can I call a trigger explicitly?
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
During trigger creation 2 tables are created
- Inserted table :- Temporary table created by SQL Server. The record, user is trying to insert is copied to inserted table
- 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.
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?
- Procedures invoke explicitly
- Procedure can be created without a table.
- Procedures accepts parameters
- Procedures can return values
- Procedures can be invoked from frond end
- create procedure for performing DML operations
- Triggers invoked implicitly.
- Triggers can not be created without a table.
- Triggers will not accept parameters
- Triggers can not return values
- Triggers can not be invoked from front end
- Create trigger to control DML operations.
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.