A trigger is a collection of SQL statements with particular names that are stored in system memory.
A trigger is a collection of SQL statements with particular names that are stored in system memory.
Triggers are special stored procedures that are executed automatically in response to the database object, database, and server events.
Every trigger has a table attached to it.
Difference between Triggers and Stored Procedures
1. A trigger is called automatically when a data modification event occurs against a table. A stored procedure, on the other hand, must be invoked directly.
2. Triggers have no chance of receiving parameters. we can pass as many parameters to a stored procedure.
3. A transaction cannot be committed or rolled back inside a trigger. We can committed or rolled back inside a stored procedure.
Common Syntax of all type of Triggers
When we use triggers?
When we need to carry out specific actions automatically in particular desirable conditions, triggers will be useful.
Types of SQL Server Triggers
1.Data Definition Language (DDL) Triggers
2. Data Manipulation Language (DML) Triggers
3. Logon Triggers
DDL Triggers
DDL triggers are fired in response to the DDL events, such as CREATE, ALTER, and DROP statements.
We can create these triggers at the database level or server level, depending on the type of DDL events.
It can also be executed in response to certain system-defined stored procedures that do DDL-like operations.
The DDL triggers are useful in the following scenario:
When we need to prevent the database schema from changing
When we need to audit changes made in the database schema
When we need to respond to a change made in the database schema
DML Triggers
DML triggers are fired in response to DML events like INSERT, UPDATE, and DELETE statements in the table or view.
It can also be executed in response to DML-like operations performed by system-defined stored procedures.
The DML triggers can be classified into two types:
A. After Triggers
After trigger fires, when SQL Server completes the triggering action successfully, that fired it.
Generally, this trigger is executed when a table completes an insert, update or delete operations.
It is not supported in views. Sometimes it is known as FOR triggers..
Click Here to view More Detail on After/For Triggers.
B. Instead Of Triggers
An INSTEAD OF trigger is a trigger that allows you to skip an INSERT, DELETE, or UPDATE
statement to a table or a view and execute other statements defined in the trigger instead.
Click Here to view More Detail on Instead of Triggers.
