Trigger In Sql Server

Trigger In Sql Server

·        Trigger is a special kind of Store procedure

·        Modifications to the table are made using INSERT,UPDATE OR DELETE  trigger will run

·        It is automatically run

·        Triggers prevent incorrect , unauthorized, or inconsistent changes to data.

Syntax in Trigger:

CREATE TRIGGER trigger_name ON table_name

FOR [INSERT/UPDATE/DELETE] AS

IF UPDATE(column_name)

[{AND/OR} UPDATE(COLUMN_NAME)...]

{ sql_statements }

Trigger Rules:

·        A table can have only three triggers action per table : UPDATE ,INSERT,DELETE.

·        Only table owners can create and drop triggers for the table.This permission cannot be transferred.

·        A trigger cannot be created on a view or a temporary table but triggers can reference them.

·        They can be used to help ensure the relational integrity of database.On dropping a table all triggers associated to the triggers are automatically dropped .

INSERT TRIGGER

·        When an INSERT trigger statement is executed ,new rows are added to the trigger table and to the inserted table at the same time.

·        The inserted table allows to compare the INSERTED rows in the table to the rows in the inserted table.

 DELETE TRIGGER

·        When a DELETE trigger statement is executed ,rows are deleted from the table and are placed in a special table called deleted table.

UPDATE TRIGGER

When an UPDATE statement is executed on a table that has an UPDATE trigger,