23 Dec 2017

What is Trigger and Types of trigger


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'



Read Complete

Popular Posts

Popular Posts

Translate

Total Pageviews