Creating Triggers:
Triggers are created using CREATE TRIGGER statement. Name of the trigger must be unique with respect to other triggers in the similar schema. The syntax for creating the trigger is:
Syntax:
CREATE OR REPLACE TRIGGER <triggername> [BEFORE/AFTER]--creating a trigger
[INSERT/UPDATE/DELETE] ON <tablename> [FOR EACH ROW] [WHEN <condition>]--perform insert, update,delete query on trigger
The subsequent example creates an easy trigger.
Example
CREATE OR REPLACE TRIGGER instremp BEFORE INSERT ON emp
BEGIN
DBMS_OUTPUT.PUT_LINE ('Inserting Record');
END;
/
The trigger gets created.
When an Insert statement is issued then the output will display like:
INSERT INTO emp (empno,ename,deptno) VALUES (1,'Giri',10);
This would display
Inserting Record
1 row created.
In the given example, the triggering statement is
BEFORE INSERT ON emp
Therefore the message 'Inserting record is displayed before the row is created
the body or action of the trigger begins from the Begin statement. There is no trigger restriction
Example
This trigger shows the total number of records available in the table if insertion is done.
CREATE OR REPLACE TRIGGER INSTR1 BEFORE INSERT ON EMP-create table
DECLARE
X NUMBER;
BEGIN
SELECT COUNT(*) INTO X FROM EMP;
DBMS_OUTPUT.PUT_LINE(X+1 ||' NUMBER OF RECORDS ARE AVAILABLE');
END;
Although insertion is done, the output will look like:
insert into emp(empno,ename,deptno) values(12,'hari',30);
15 number of records are available
Before insertion the total records were 14. After insertion the total records raise to 15. Note here, there is an explicit DECLARE statement and no IS/AS is specfiied. If a trigger holds any variable, the DECLARE must be specified.
The trigger execution is performed in the subsequent ways:
1. All the Before statement triggers are executed.
2. The Before Row Level triggers are executed.
3. After Row Level triggers are executed.
4. After Statement triggers are executed that apply to the statement.