TRIGGERS

TRIGGERS

TRIGGERS: Triggers are one of the database events, which performs their own operation when user performs any INSERT, UPDATE, DELETE  operations on a specific table.

                                                OR

Trigger is a type of stored procedure that implicitly executed when user performs DML operation on the table. It will not accept any parameters.

Types of Triggers:

·         AFTER Trigger

·         INSTEAD OF Trigger

·         DDL Triggers

After Triggers:

These are the triggers, which performs their own operation after performing insert, delete, and update operations on a specific table.

Syntax:

CREATE TRIGGER TRIGGERNAME ON TABLE NAME

FOR/ AFTER {INSERT,/ UPDATE/ DELETE}

 AS

BEGIN

 SQL STATEMENT.

END

INSERT TRIGGER: This trigger fires when user performs insert operation on the table. When user insert a record into the table the temporary table called inserted is created the newly inserted record is also stored in inserted table temporarily

DELETE TRIGGER: This trigger fires when user performs delete operation on the table. When user delete a record from the table the temporary table called deleted is created the deleted record is also stored in deleted table temporarily

UPDATE TRIGGER: This trigger fires when user performs update operation on the table. When user update a record into the table the temporary tables called inserted  and deleted are created the new values placed into inserted table and old values will be placed in deleted table temporarily

E.g.1:

CREATE TRIGGER T1 ON DEPT FOR INSERT

AS

BEGIN

INSERT INTO DEPT1 SELECT * FROM INSERTED

END

The above trigger fires after performing INSERT operation on DEPT table. It will inserts the newly inserted records into DEPT1

E.g.2:

CREATE TRIGGER T2 ON DEPT FOR DELETE

AS

BEGIN

INSERT INTO DEPT2 SELECT * FROM DELETED

END

The above trigger fires after performing DELETE operation on DEPT table. It will inserts the deleted records into DEPT2

E.g.3:

CREATE TRIGGER T3 ON DEPT FOR UPDATE

AS

BEGIN

INSERT INTO DEPTO SELECT * FROM DELETED

INSERT INTO DEPTN SELECT * FROM INSERTED

END

The above trigger fires after performing UPDATE operation on DEPT table. It will

Inserts the newly modified records into DEPTN and inserts old values into DEPTO.

Instead of Triggers: These are the triggers, which performs their operations instead of performing user specified operations.

Syntax:

CREATE TRIGGER TRIGGERNAME ON TABLE NAME

INSTEAD OF {INSERT,/ UPDATE/ DELETE}

 AS

BEGIN

 SQL STATEMENT.

END

E.g1:

CREATE TRIGGER T4 ON DEPT INSTEAD OF INSERT,UPDATE, DELETE

AS

BEGIN

PRINT ‘THESE OPERATIONS ARE NOT ALLOWED’

END

The above trigger fires automatically and shows a message THESE OPERATIONS ARE NOT ALLOWED, when user try to perform INSERT, UPDATE, DELETE operations on DEPT table.

SP_HELPTRIGGER: This stored procedure is used to display the list of triggers which been placed on a specific table.

Syntax: SP_HELPTRIGGER TABLENAME

                   SP_HELPTRIGGER DEPT

DISPLAYING THE CODE OF TRIGGER:

Syntax: SP_HELPTEXT  ‘TRIGGER_NAME’

                   SP_HELPTEXT  ‘T1’

DDL TRIGGERS (2005 Triggers):

These triggers are fired when user performs DDL operations in the database and these triggers belong to database. It  means we can define triggers on the current database.

Syntax:

CREATE TRIGGER TRIGGERNAME ON DATABASE

FOR/ AFTER {DROP,/ALTER/ CREATE}

 AS

BEGIN

 SQL STATEMENT.

END

E.g.:

USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE FOR DROP-TABLE

AS

BEGIN

PRINT ‘TABLE DROPPED’

ROLLBACK

END

E.g.:

USE PUBS

CREATE TRIGGER DROP_TRG ON DATABASE  INSTEAD OF DROP-TABLE

AS

BEGIN

PRINT ‘U CANNOT DROP THE TABLE’

ROLLBACK

END