Types of PL/SQL Triggers
Trigger timing and operations forms different combinations such as BEFORE INSERT OR BEFORE DELETE OR BEFORE UPDATE .BEFORE and AFTER are known as conditional triggers.
Conditional Trigger: Before
Trigger is activated before the operation on the table or view is performed.
Query:
-- Create Geeks table
CREATE TABLE Geeks (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- Insert into Geeks Table
INSERT INTO Geeks (Id, Name, Score) VALUES (1, 'Sam', 800);
INSERT INTO Geeks (Id, Name, Score) VALUES (2, 'Ram', 699);
INSERT INTO Geeks (Id, Name, Score) VALUES (3, 'Tom', 250);
INSERT INTO Geeks (Id, Name, Score) VALUES (4, 'Om', 350);
INSERT INTO Geeks (Id, Name, Score) VALUES (5, 'Jay', 750);
-- insert statement should be written for each entry in Oracle Sql Developer
CREATE TABLE Affect (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER BEFORE_INSERT
BEFORE INSERT ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:NEW.Id, :NEW.Name, :NEW.Score);
END;
/
INSERT INTO Geeks (Id, Name, Score) VALUES (6, 'Arjun', 500);
BEFORE DELETE Trigger
-- BEFORE DELETE trigger
CREATE OR REPLACE TRIGGER BEFORE_DELETE
BEFORE DELETE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
END;
/
DELETE FROM Geeks WHERE Id = 3;
BEFORE UPDATE Trigger
-- BEFORE UPDATE trigger
CREATE OR REPLACE TRIGGER BEFORE_UPDATE
BEFORE UPDATE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
END;
/
UPDATE Geeks SET Score = 900 WHERE Id = 5;
SELECT * FROM Affect;
SELECT * FROM Geeks;
Output:
Explanation:
- BEFORE_INSERT Trigger is fired before adding a row in Geeks Table,and row is inserted in the Affect table.
- BEFORE_DELETE Trigger is activated before the row is delete from the Geeks table and row which satisfy the condition is added to Affect table.
- BEFORE_UPDATE TRIGGER is activated before the row with Id=5 is updated and row with old values is added to Affect table
Geeks table after trigger events
Conditional Trigger: After
Trigger is activated after the operation on the table or view is performed.
Query:
SET SERVEROUTPUT ON;
CREATE TABLE Geeks (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- Insert into Geeks Table
INSERT INTO Geeks (Id, Name, Score) VALUES (1, 'Sam', 800);
INSERT INTO Geeks (Id, Name, Score) VALUES (2, 'Ram', 699);
INSERT INTO Geeks (Id, Name, Score) VALUES (3, 'Tom', 250);
INSERT INTO Geeks (Id, Name, Score) VALUES (4, 'Om', 350);
INSERT INTO Geeks (Id, Name, Score) VALUES (5, 'Jay', 750);
-- insert statement should be written for each entry in Oracle Sql Developer
CREATE TABLE Affect (
Id INT,
Name VARCHAR2(20),
Score INT
);
SELECT * FROM Geeks;
-- AFTER DELETE trigger
CREATE OR REPLACE TRIGGER AFTER_DELETE
AFTER DELETE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:OLD.Id, :OLD.Name, :OLD.Score);
END;
/
DELETE FROM Geeks WHERE Id = 4;
-- AFTER UPDATE trigger
CREATE OR REPLACE TRIGGER AFTER_UPDATE
AFTER UPDATE ON Geeks
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:NEW.Id, :NEW.Name, :NEW.Score);
END;
/
UPDATE Geeks SET Score = 1050 WHERE Id = 5;
SELECT * FROM Affect;
SELECT * FROM Geeks;
Output:
Explanation: After the deletion of the row from the Geek table trigger is fired and the row which is deleted is added to the Affect Table.In second trigger i.e After_update trigger is fired after performing update on Geeks table and the row is added to Affect Table.Output contains the Affect table and the Geek table after the trigger events.
PL/SQL Triggers
PL/SQL stands for Procedural Language/ Structured Query Language. It has block structure programming features.PL/SQL supports SQL queries. It also supports the declaration of the variables, control statements, Functions, Records, Cursor, Procedure, and Triggers.PL/SQL contains a declaration section, execution section, and exception-handling section. Declare and exception handling sections are optional.
Syntax:
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
Contact Us