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
No comments:
Post a Comment