Thursday , February 21 2019
Home / DataBase / SQL SERVER / what is Trigger and Types of Triggers in SQL Server

what is Trigger and Types of Triggers in SQL Server

Triggers in Sql Server:-

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.A trigger is executed implicitly whenever the triggering event happens, which performs their own operation when user performs any INSERT, UPDATE, DELETE operations on a specific table.

In sql server there are 3 types of triggers

1.DML triggers
2.DDL triggeers

1.DML triggers:

A DML trigger is fired on an INSERT, UPDATE, or DELETE operation on a database table. It can be fired after the statement executes, and can be fired once per affected row or once per statement.

DML triggers can be again classified into 2 types

a.After triggers or For triggers
b.Instead of triggers

a.After triggers or For triggers:-

AFTER triggers are executed after the action of the INSERT, UPDATE, or DELETE statement is performed.Specifying AFTER is the same as specifying FOR. AFTER triggers can be specified only on tables.

b.Instead of triggers:-

Instead of triggers performs their operations instead of performing user specified operations.INSTEAD OF triggers can also be defined on views with one or more base tables.

see more for creating DML Triggers step step by Step

2.DDL Triggers.

DDL triggers fire in response to a variety of Data Definition Language (DDL) events. These events primarily correspond to Transact-SQL statements that start with the keywords CREATE, ALTER, DROP, GRANT, DENY, REVOKE or UPDATE STATISTICS.

Please Refer this link for creating DDL Triggers step step by Step


Did you find this post as useful? I hope you liked the article. Please share your valuable suggestions and feedback in the comments.


Print Friendly, PDF & Email

Give us your valuable feedback


About Samba

Check Also

sql server backup script

Backup all databases in SQL Instance with one script

Backup: A copy of SQL Server data that can be used to restore and recover the …