Trigger

-> A trigger is an SQL procedure that initiates an action when an event (INSERT, DELETE or UPDATE) occurs. Triggers are used to maintain the referential integrity of data by changing the data in a systematic fashion. A trigger cannot be called or executed. DBMS automatically fires the trigger as a result of a data modification to the associated table.
-> It is a database object which is bound to a table and is executed automatically. You can’t explicitly invoke triggers. The only way to do this is by performing the required action in the table that they are assigned to.

Types of Triggers
-> SQL server includes three general types of triggers: DML triggers, DDL triggers, and logon triggers.

DDL triggers
-> DDL triggers are invoked when a data definition language (DDL) event takes place in the server or database.
-> These events primarily correspond to Transact SQL statements that start with the keywords CREATE, ALTER and DROP.
-> The below example shows how a DDL trigger can be used to prevent any table in a database from being modified or dropped.

DML triggers
-> A DML trigger is an action programmed to execute when a data manipulation language (DML) event occurs in the database server. DML events include UPDATE, INSERT, or DELETE statements issues against a table or a view.

Types of DML Triggers

  • AFTER Triggers
  • INSTEAD OF Triggers

After Triggers
-> These triggers run after an INSERT, UPDATE or DELETE on a table.
-> They are not supported for views.

1) AFTER INSERT Trigger
2) AFTER UPDATE Trigger
3) AFTER DELETE Trigger

Instead of Triggers
-> These can be used as an interceptor for anything that anyone tried to do on a table or view.
-> INSTEAD OF triggers override  the standard actions of the triggering statement : INSERT, UPDATE or DELETE. An INSTEAD OF trigger can be defined to perform error or value checking on one or more columns and then perform additional actions before inserting the record.

After Insert Trigger
-> This trigger is fired after an INSERT on the table.

-> The CREATE TRIGGER statement is used to create the trigger. The ON clause specifies the table name on which the table name is to be attached. The FOR INSERT specifies that this is an After Insert trigger. In place of FOR INSERT, AFTER INSERT can be used.

AFTER UPDATE Trigger
-> This trigger is fired after an update on the table.

Leave a Reply

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