What is Trigger and Types of trigger
TRIGGERS : DATABASE OBJECTS USED TO STORE ANY TYPE OF T-SQL STATEMENT.
THE STATEMENTS ARE AUTO EXECUTED IN RESPONSE TO DML & DDL EVENTS
>>>>DML EVENTS : INSERT, UPDATE, DELETE
TRIGGERS DEFINED ON THESE DML EVENTS ARE CALLED "DML TRIGGERS"
TYPES OF DML TRIGGERS:\
1. FOR / AFTER TRIGGERS : Trigger code executed in ADDITION to actual operation
2. INSTEAD OF TRIGGERS : Trigger code executed in PLACE OF actual operation
DURING DML TRIGGERS : TWO MEMORY ARE AUTO CREATED.
ONE FOR AUDITTING THE ROW(S) BEING INSERTED INTO TABLE : "inserted" memory table.
ONE FOR AUDITTING THE ROW(S) BEING REMOVED FROM TABLE : "deleted" memory table.
-------------------------------------------------------------------------------------
>>>>DDL EVENTS : CREATE, ALTER, DROP
TRIGGERS DEFINED ON THESE DDL EVENTS ARE CALLED "DDL TRIGGERS"
TYPES OF DML TRIGGERS:
1. FOR / AFTER TRIGGERS : Trigger code executed in ADDITION to actual operation
CREATE DATABASE TRIGGER_DB
USE TRIGGER_DB
CREATE TABLE RESERVATION_TABLE
(
Railway_Code nvarchar(30),
No_Seats int,
ClassofCode varchar(10)
)
INSERT INTO RESERVATION_TABLE VALUES ('Ra01', 24, 'XX')
CREATE TRIGGER Trig
ON RESERVATION_TABLE
FOR -- IN ADDITION TO
INSERT, UPDATE, DELETE
AS
PRINT 'GIVEN INSERT / UPDATE / DELETE OPERATION IS SUCCESSFUL'
-- TEST THE TRIGGER:
INSERT INTO RESERVATION_TABLE VALUES ('AI01', 11, 'EX') -- TRIGGER AUTO EXECUTES.
INSERT INTO RESERVATION_TABLE VALUES ('AI02', 22, 'EX') -- TRIGGER AUTO EXECUTES.
REQUIREMENT: RESERVATIONS ONCE CONFIRMED CANNOT BE MODIFIED / CANCELLED.
REQUIREMENT: HOW TO DISABLE UPDATES & DELETES ON THE TABLE?
CREATE TRIGGER Trig1
ON RESERVATION_TABLE
INSTEAD OF -- IN PLACE OF
UPDATE, DELETE
AS
PRINT 'RESERVATIONS ONCE CONFIRMED CANNOT BE MODIFIED/CANCELLED'
UPDATE RESERVATION_TABLE
SET No_Seats = No_Seats - 1
WHERE Railway_Code = 'AI01'
DELETE FROM RESERVATION_TABLE
SELECT * FROM RESERVATION_TABLE
SYNTAX FOR TRIGGER CREATION:
CREATE TRIGGER TRIG_NAME
ON TABLE / VIEW
FOR | INSTEAD OF
<DML EVENT(S): insert, update, delete>
AS
BEGIN
...............
..................
...............
END
PURPOSE OF TRIGGERS:
1. TO PEFORM ADDITIONAL OPERATIONS IN RESPONSE TO DML / DDL EVENTS
2. TO PERFORM ALTERNATE OPERATIONS IN PLACE OF DML / DDL EVENTS
3. TO DISTRIBUTE DATA TO MULTIPLE TABLES
CREATE TABLE Rail
(
Railway_Code NVARCHAR(30),
SOURCE_CITY VARCHAR(30),
DEST_CITY VARCHAR(30)
)
CREATE VIEW VW_Rail_RESERVATION
AS
SELECT
F.Railway_Code, F.SOURCE_CITY, F.DEST_CITY,
R.ClassofCode, R.No_Seats FROM RESERVATION_TABLE R
JOIN
Rail F
ON
R.Railway_Code = F.Railway_Code
-- ISSUE:
INSERT INTO VW_Rail_RESERVATION VALUES ('Ra01', 'HYB', 'NYC', 'XX', '9')
-- REASON: WE DID NOT SPECIFY THE LOGIC FOR VALUE DISTRIBUTION TO BASE TABLES
-- SOLUTION TO ABOVE ISSUE: TRIGGERS
-- PLAN: AUDIT EVERY ROW BEING INSERTED. SCAN ALL THE DATA. USING "INSERTED" TABLE
-- IDENTIFY RELEVANT DATA FOR FLIGHT TABLE, INSERT.
-- IDENTIFY RELEVANT DATA FOR RESERVATION TABLE, INSERT.
CREATE TRIGGER trigdatadistribution
ON VW_Rail_RESERVATION
INSTEAD OF
INSERT
AS
BEGIN
DECLARE @Railway_Code NVARCHAR(30), @SRC VARCHAR(30), @DEST VARCHAR(30)
DECLARE @SEATS INT, @CLASS VARCHAR(30)
SELECT @Railway_Code = Railway_Code FROM INSERTED
SELECT @SRC = SOURCE_CITY FROM INSERTED
SELECT @DEST = DEST_CITY FROM INSERTED
SELECT @SEATS = No_Seats FROM INSERTED
SELECT @CLASS = ClassofCode FROM INSERTED
INSERT INTO Rail VALUES (@Railway_Code,@SRC,@DEST)
INSERT INTO RESERVATION_TABLE VALUES (@Railway_Code, @SEATS, @CLASS)
END
INSERT INTO VW_Rail_RESERVATION VALUES ('Ra99', 'HYB', 'NYC', 'EX', '9')
INSERT INTO VW_Rail_RESERVATION VALUES ('Ra88', 'HYB8', 'NYC8', 'EX', '8')
SELECT * FROM Rail
SELECT * FROM RESERVATION_TABLE
-- DATABASE SCOPED DDL TRIGGERS
CREATE TRIGGER TRIGER_DBLEVEL_DDL
ON DATABASE
FOR
CREATE_TABLE, ALTER_TABLE, DROP_FUNCTION, CREATE_TRIGGER, ALTER_PROCEDURE
AS
PRINT 'GIVEN DATABASE LEVEL DDL OPERATION IS SUCCESSFUL'
EX:
ALTER TABLE FLIGHT ADD STS BIT
-- SERVER SCOPDED DDL TRIGGERS
CREATE TRIGGER TRIGER_SERVERLEVEL_DDL
ON ALL SERVER
FOR
CREATE_DATABASE, ALTER_DATABASE, DROP_DATABASE
AS
PRINT 'GIVEN SERVER LEVEL DDL OPERATION IS SUCCESSFUL'
No comments:
Post a Comment