Definition: – A set of PL/SQL statements stored permanently in database and automatically activated when ever an event raising statement (DML) is executed.
They are stored in USER_TRIGGERS system table
They are used to impose business rules or user defined restrictions on table columns.
They are also activated when tables are manipulated by other application software tools.
They provide high security.
It will execute implicitly whenever the triggering event happens and trigger does not accept arguments.
The act of executing a trigger is known as firing
The trigger event can be a DML (INSERT, UPDATE, or DELETE) operation on database table or certain kinds of views; or system event, such as database startup or shutdown, and certain kinds of DDL operations.
INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view
Trigger Parts: – Four parts are there.
Triggering event
Trigger types
Trigger restriction
Trigger body
Triggering event:- Indicates when to activate the trigger
BEFORE –INSERT/UPDATE/DELETE
AFTER –INSERT/UPDATE/DELETE
Trigger types:- Two types of Triggers are there
Row level trigger- Activates the trigger for every row manipulated by DML statement
Statement level trigger- Activates the trigger only one’s for one DML statement(default
type).
Trigger Restriction: – Used to stop the activation of trigger based on condition. If condition is
TRUE trigger is active.
4. Trigger body: – A set of PL/SQL statements.
NEW and OLD (pseudo columns):-
Used to retrieve data from DML statement temporary buffer to trigger body
Valid with ROW level trigger only
NEW supports with
Explanation About syntax:-
CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time the trigger should get fired. i.e. for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. Before and after cannot be used to create a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
[OF column_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
[ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
[FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e. statement level Trigger).
WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
They are stored in USER_TRIGGERS system table
They are used to impose business rules or user defined restrictions on table columns.
They are also activated when tables are manipulated by other application software tools.
They provide high security.
It will execute implicitly whenever the triggering event happens and trigger does not accept arguments.
The act of executing a trigger is known as firing
The trigger event can be a DML (INSERT, UPDATE, or DELETE) operation on database table or certain kinds of views; or system event, such as database startup or shutdown, and certain kinds of DDL operations.
INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view
Trigger Parts: – Four parts are there.
Triggering event
Trigger types
Trigger restriction
Trigger body
Triggering event:- Indicates when to activate the trigger
BEFORE –INSERT/UPDATE/DELETE
AFTER –INSERT/UPDATE/DELETE
Trigger types:- Two types of Triggers are there
Row level trigger- Activates the trigger for every row manipulated by DML statement
Statement level trigger- Activates the trigger only one’s for one DML statement(default
type).
Trigger Restriction: – Used to stop the activation of trigger based on condition. If condition is
TRUE trigger is active.
4. Trigger body: – A set of PL/SQL statements.
NEW and OLD (pseudo columns):-
Used to retrieve data from DML statement temporary buffer to trigger body
Valid with ROW level trigger only
NEW supports with
Explanation About syntax:-
CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
{BEFORE | AFTER | INSTEAD OF } – This clause indicates at what time the trigger should get fired. i.e. for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. Before and after cannot be used to create a trigger on a view.
{INSERT [OR] | UPDATE [OR] | DELETE} – This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
[OF column_name] – This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.
CREATE [OR REPLACE] TRIGGER <trigger_name> – This clause creates a trigger with the given name or overwrites an existing trigger with the same name.
[ON table_name] – This clause identifies the name of the table or view to which the trigger is associated.
[REFERENCING OLD AS o NEW AS n] – This clause is used to reference the old and new values of the data being changed. By default, you reference the values as: old.column_name or: new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.
[FOR EACH ROW] – This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e. statement level Trigger).
WHEN (condition) – This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
ROW LEVEL TRIGGER
The trigger body executes once for each row affected by the triggering event. A row level trigger is not executed if the triggering event affects no rows.
Row level triggers are useful if the trigger action depends on data of rows that are affected or on data provided by the triggering event itself.
Example:-1
/* here we are going to write trigger for automatically updating the ‘ename’ when ever we insert the record into ‘emp’ which is shown below*/
SQL>CREATE OR REPLACE TRIGGER up_ename
BEFORE INSERT
ON emp
FOR EACH ROW
BEGIN
:NEW.ename := UPPER (:NEW.ename);
END;
– Trigger created.
Now we are going to insert record into ‘emp’ table
/* here we are going to insert ‘ename’ in LOWER case*/
SQL> INSERT INTO emp (empno, ename, sal, comm, deptno)
VALUES (7711, 'andrew',3500, 500, 10);
Output:-
SELECT * FROM emp WHERE empno=7711;
7711 ANDREW 3500 500 10
/* here we can see that ename is in UPPER case, now we can conclude that above trigger what we had written is fired at this event*/
Example2:-
/*here we trying to decrease the salary by updating then below trigger will raise*/
CREATE OR REPLACE TRIGGER up_sal BEFORE UPDATE ON emp FOR EACH ROW
BEGIN
IF :NEW.sal<:OLD.sal THEN
RAISE_APPLICATION_ERROR(-20000,'updating salary must greater than existing salary');
END IF;
END;
Now we are going to decrease the ‘sal’
UPDATE emp SET sal=sal-1000 WHERE empno=7839;
Output:-
/* here we can see that trigger raised*/
Error report:
SQL Error: ORA-20000: updating salary must greater than existing salary
ORA-06512: at "APPS.UP_SAL"
ORA-04088: error during execution of trigger 'APPS.UP_SAL'
Statement level Trigger:-
Activates the trigger for only once for one DML statement.
A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all.
Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: for example, a trigger that performs a complex security check on the current user.
Example:-
/*here we are trying to INSERT record INTO ‘emp’ table in week ends…. Below trigger will fire when we try to insert record in week ends*/
CREATE OR REPLACE TRIGGER sec_emp
BEFORE INSERT ON emp
BEGIN
IF TO_CHAR (SYSDATE,'DY') in ('SAT','SUN') THEN
RAISE_APPLICATION_ERROR (-20101,'in week ends we can not insert record');
END IF;
END;
Now are trying to insert into emp in week ends and we can see the output below
/* Before inserting change the system date to saturday or sunday date */ SQL>INSERT INTO EMP(EMPNO, ENAME, DEPTNO) VALUES (7913, ’mike’, 10); Output:- Error report: SQL Error: ORA-20101: in week ends we can not insert record ORA-06512: at “APPS.SEC_EMP” ORA-04088: error during execution of trigger ‘APPS.SEC_EMP’
RAISE_APPLICATION_ERROR:- it is built in function that stops the DML operation and displays the error message.
STATEMENT LEVEL TRIGGER
Statement level Trigger:-
Activates the trigger for only once for one DML statement.
A statement trigger is fired once on behalf of the triggering event, even if no rows are affected at all.
Statement triggers are useful if the trigger action does not depend on the data from rows that are affected or on data provided by the triggering event itself: for example, a trigger that performs a complex security check on the current user.
Example:-
/*here we are trying to INSERT record INTO ‘emp’ table in week ends…. Below trigger will fire when we try to insert record in week ends*/
CREATE OR REPLACE TRIGGER sec_emp
BEFORE INSERT ON emp
BEGIN
IF TO_CHAR (SYSDATE,'DY') in ('SAT','SUN') THEN
RAISE_APPLICATION_ERROR (-20101,'in week ends we can not insert record');
END IF;
END;
Now are trying to insert into emp in week ends and we can see the output below
/* Before inserting change the system date to saturday or sunday date */ SQL>INSERT INTO EMP(EMPNO, ENAME, DEPTNO) VALUES (7913, ’mike’, 10); Output:- Error report: SQL Error: ORA-20101: in week ends we can not insert record ORA-06512: at “APPS.SEC_EMP” ORA-04088: error during execution of trigger ‘APPS.SEC_EMP’
RAISE_APPLICATION_ERROR:- it is built in function that stops the DML operation and displays the error message.
Trigger Execution Hierarchy
PL/SQL Trigger Execution Hierarchy
The following is the sequence in which different triggers are fired.
Before statement level
Before row level
After row level
After statement level
For Example: Let’s create a table ‘test’ to store messages when triggers are fired.
CREATE TABLE test
(Message varchar2(2000));
1) BEFORE UPDATE, Statement Level: This trigger will insert a record into the table ‘test’ before a sql update statement is executed, at the statement level.
CREATE OR REPLACE TRIGGER emp_sal_upd_stmt_level_trigBEFORE UPDATE ON emp
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating employees ');
INSERT INTO test values('Stmt level Before update - Updating employees ');
END;
2) BEFORE UPDATE, Row Level: This trigger will insert a record into the table ‘test’ before each row is updated.
CREATE OR REPLACE TRIGGER emp_sal_upd_stmt_level_trig
BEFORE UPDATE ON emp
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.sal - :OLD.sal;
DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff);
Insert into test values('Row level Before update - Updating employee ' || :OLD.empno);
END;
/
3) AFTER UPDATE, Statement Level: This trigger will insert a record into the table ‘product_check’ after a sql update statement is executed, at the statement level.
CREATE or REPLACE TRIGGER emp_sal_aftr_upd_stmt_level_trig
AFTER
UPDATE ON emp
BEGIN
DBMS_OUTPUT.PUT_LINE('Updating employees ');
INSERT INTO test values('Stmt level After update - Updating employees ');
END;
/
4) AFTER UPDATE, Row Level: This trigger will insert a record into the table ‘product_check’ after each row is updated.
CREATE OR REPLACE TRIGGER emp_sal_aftr_upd_row_level_trig
AFTER UPDATE ON emp
FOR EACH ROW
DECLARE
sal_diff NUMBER;
BEGIN
sal_diff := :NEW.sal - :OLD.sal;
DBMS_OUTPUT.PUT_LINE('Updating employee ' || :OLD.empno);
DBMS_OUTPUT.PUT_LINE('..Old salary: ' || :OLD.sal);
DBMS_OUTPUT.PUT_LINE('..New salary: ' || :NEW.sal);
DBMS_OUTPUT.PUT_LINE('..Raise : ' || sal_diff);
INSERT INTO test values('Row level after update - Updating employee ' || :OLD.empno);
END;
/
Now lets execute a update statement on table emp.
UPDATE emp SET sal = 2000
WHERE deptno = 10;
Let’s check the data in test table to see the order in which the trigger is fired.
SQL>SELECT * FROM test;
Mutating Triggers
When we perform two DML operations at one’s then it leads to ‘Trigger Mutating’ error while working with software tools.
If trigger applied on table1 (before insert on table1) performing any other DML or DRL on same table(table1) in trigger body causes “Mutating Error”.
Example1:-
/*here we are deleting the row and at the same time we are selecting(DRL) the record then it leads to mutating error*/
CREATE OR REPLACE TRIGGER mut_trig
AFTER DELETE ON emp FOR EACH ROW
DECLARE
x number;
BEGIN
SELECT sal
INTO x
FROM emp
WHERE ename='SMITH';
INSERT INTO emp(empno,ename,deptno) VALUES(9999,'APPS',10);
DBMS_OUTPUT.PUT_LINE('SALARY =>'||x);
END;
Now we can delete the record from ‘emp’
SQL>DELETE FROM emp;
Output:-
Error starting at line 'x' in command:
DELETE FROM emp
Error report:
SQL Error: ORA-04091: table APPS.EMP is mutating, trigger/function may not see it
ORA-06512: at "APPS.MUT_TRIG", line 'y'
ORA-04088: error during execution of trigger 'APPS.MUT_TRIG'
Example2:-
/*here we are deleting the row and at the same time we are updating(DML) the record then it leads to mutating error*/
CREATE OR REPLACE TRIGGER mutuating_trig
AFTER DELETE ON emp FOR EACH ROW
DECLARE
BEGIN
UPDATE emp SET sal=sal+sal*.05
WHERE ename='SMITH';
END;
Now we can delete the record from ‘emp’
SQL>DELETE FROM emp;
Output:-
Error starting at line 'x' in command:
DELETE FROM emp
Error report:
SQL Error: ORA-04091: table APPS.EMP is mutating, trigger/function may not see it
ORA-06512: at "APPS.MUTUATING_TRIG", line 'y'
ORA-04088: error during execution of trigger 'APPS.MUTUATING_TRIG'
No comments:
Post a Comment