Triggers in oracle are blocks of PL/SQL code which oracle engine can execute automatically based on some action or event.
These events can be:
Triggers are automatically and repeatedly called upon by oracle engine on satisfying certain condition.
Triggers can be activated or deactivated depending on the requirements.
If triggers are activated then they are executed implicitly by oracle engine and if triggers are deactivated then they are executed explicitly by oracle engine.
Here we have mentioned a few use cases where using triggers proves very helpful:
Whenever a trigger is created, it contains the following three sequential parts:
The above diagram clearly indicated that Triggers can be classified into three categories:
which are further divided into different parts.
There are 2 different types of level triggers, they are:
FOR EACH
ROW clause in a triggering statement.
There are 3 different types of event triggers, they are:
There are 2 different types of timing triggers, they are:
Following is the syntax for creating a trigger:
CREATE OR REPLACE TRIGGER <trigger_name>
BEFORE/AFTER/INSTEAD OF
INSERT/DELETE/UPDATE ON <table_name>
REFERENCING (OLD AS O, NEW AS N)
FOR EACH ROW WHEN (test_condition)
DECLARE
-- Variable declaration;
BEGIN
-- Executable statements;
EXCEPTION
-- Error handling statements;
END <trigger_name>;
END;
where,
CREATE OR REPLACE TRIGGER
is a keyword used to create a trigger and <trigger_name> is user-defined where a trigger can be given a name.
BEFORE/AFTER/INSTEAD OF
specify the timing of the trigger's occurance. INSTEAD OF
is used when a view is created.
INSERT/UPDATE/DELETE specify the DML statement.
<table_name> specify the name of the table on which DML statement is to be applied.
REFERENCING
is a keyword used to provide reference to old and new values for DML statements.
FOR EACH ROW
is the clause used to specify row level tigger.
WHEN
is a clause used to specify condition to be applied and is only applicable for row-level trigger.
DECLARE
, BEGIN
, EXCEPTION
, END
are the different sections of PL/SQL code block containing variable declaration, executable statements, error handling statements and marking end of PL/SQL block respectively where DECLARE and EXCEPTION part are optional.
Below we have a simple program to demonstrate the use of Triggers in PL/SQL code block.
CREATE OR REPLACE TRIGGER CheckAge
BEFORE
INSERT OR UPDATE ON student
FOR EACH ROW
BEGIN
IF :new.Age>30 THEN
raise_application_error(-20001, 'Age should not be greater than 30');
END IF;
END;
Trigger created.
Following is the STUDENT table,
ROLLNO | SNAME | AGE | COURSE |
---|---|---|---|
11 | Anu | 20 | BSC |
12 | Asha | 21 | BCOM |
13 | Arpit | 18 | BCA |
14 | Chetan | 20 | BCA |
15 | Nihal | 19 | BBA |
After initializing the trigger CheckAge
, whenever we will insert any new values or update the existing values in the above table STUDENT our trigger will check the age before executing INSERT
or UPDATE
statements and according to the result of triggering restriction or condition it will execute the statement.
Let's take a few examples and try to understand this,
Example 1:
INSERT into STUDENT values(16, 'Saina', 32, 'BCOM');
Age should not be greater than 30
Example 2:
INSERT into STUDENT values(17, 'Anna', 22, 'BCOM');
1 row created
Example 3:
UPDATE STUDENT set age=31 where ROLLNO=12;
Age should not be greater than 30
Example 4:
UPDATE STUDENT set age=23 where ROLLNO=12;
1 row updated.