Triggers and Types in sql server

What is trigger

Triggers is a special type of database operation (In many way it is similar to a stored procedure) which execute automatically when perform DML (INSERT, UPDATE, DELETE) operation on the table or views or perform DDL (CREATE, AKTER, DROP) operation on Table, Function, Index, Store Procedure or a database operation (LOGON, LOGOFF).
Each table or views has its own triggers and you can't execute trigger explicitly.

Type of Trigger in sql

• DML Trigger
       DML Trigger again classified into Two Types.
  • (i.) AFTER Trigger (For Trigger)
             AFTER Trigger again classified into Three Types
    • AFTER INSERT Trigger
    • AFTER UPDATE Trigger.
    • AFTER DELETE Trigger.
  • (ii.) INSTEAD of Triggers
            INSTEAD OF Triggers again classified into Three Types
    • INSTEAD OF INSERT Trigger
    • INSTEAD OF UPDATE Trigger.
    • INSTEAD OF DELETE Trigger.
• DDL Trigger
• LOGON Trigger
• CLR Trigger

AFTER Triggers (For Triggers)

AFTER Triggers fire after the triggering action (Insert, Update, delete) but before the statement's competition to the databases. AFTER triggers has the ability that it can rollback modification action or statements.
e.g. If we insert a row into a table and a trigger associated with that insert event on the table will fire only after the row passes perfectly (with primary key, constraints etc.). If insertion fails due to any reason, then SQL Server will not fire the AFTER Trigger.

Note:-
1. AFTER trigger can only use with table not views.
2. A single AFTER trigger can use with single table.
3. The text, ntext, and image columns cannot be referenced in the AFTER trigger logic.

INSTEAD OF Triggers

INSTEAD OF Triggers fire instead of any of the Insert, Update or Delete operations i.e this trigger fire before SQL Server starts the execution of the action. If We have an INSTEAD OF insert/update/delete trigger on a table that successfully executed but does not include the actual insert/update/delet to the table.
e.g. If we apply an INSTEAD OF trigger on a table for the delete operation, and we try to delete rows from the table, they will not actually get deleted.

Note:-
1. We can define an INSTEAD OF trigger on a view. When multiple base table need to update than use view in trigger.
2. INSTEAD OF trigger replace the actions of the original data modification that fired the trigger.
3. Constraint(Foreign key, check, unique, primary) happens after the INSTEAD OF trigger fires.

DDL Trigger

DDL Trigger fire in response to DDL Events (when Create, Alter, Drop (Table, Function, Index, Store Procedure)) then corresponding event fired.
e.g. when you create a table using create DDL statement, associate event is "create_table", that event is raised. And you have trigger that associate with that event than, when you create a table that associate table is fired.
Similarly when drop a store procedure than "Drop_storeprocedure" is fire, when create a function DDL function event is raised.

DDL Triggers can be DATABASE scoped (CREATE TABLE, CREATE PROCEDURE, CREATE FUNCTION, ALTER TABLE, ALTER PROCEDURE, ALTER FUNCTION etc.) or SERVER scoped (CREATE DATABASE, CREATE LOGIN, GRANT_SERVER, ALTER DATABASE, ALTER LOGIN etc.).

Use of DDL Trigger

If you want to execute some code in response to a specific DDL Event To prevent certain changes to your database schema. Audit the changes that the users are making to the database structure.

Note:-
1. We can use only FOR/AFTER clause in DDL Triggers not INSTEAD OF clause.
2. List of all list of DDL events

LOGON Trigger

Logon Trigger fire when login event occurs. LOGON Trigger fire after authentication phase of logging finishes, but before the user session is established. This Trigger control server sessions such as
1. Tracking login activity.
2. Restricting Login to SQL Server.
3. Limiting number of session for specific login.

CLR Trigger

CLR Trigger allows for the database objects (such as trigger both DDL and DML) to be coded in .NET using c#, VB. CLR Trigger is based on CLR. CLR introduce with SQL Server 2008

Next Related Post