Database Triggers
Triggers are named PL/SQL blocks with declarative, executable, and exception handling sections
A trigger is executed implicitly whenever the triggering event takes place
Triggers do not accept arguments
Like packages, triggers are stored database objects and can’t be local to a block
Stored Procedures vs Database Triggers
DML Triggers
Application:
To impose complex integrity constraints not possible through declarative constraints
To audit information in a table
To create replica of a table etc.
Security reasons
Types of Triggers
DML Triggers
DML Triggers are fired by the execution of DML statements
DDL Triggers
DDL triggers execute every time a DDL statement is executed
Database Triggers
Database triggers are fired during system events like startup/shutdown and user events like logon/logoff
DML Trigger Components
Statement Triggers
The trigger body executes only once for the triggering event. This is the default.
Syntax:
CREATE [ OR REPLACE ] TRIGGER trigger_name
trigger_timing event1 [ OR event2 OR event3 ]
ON table_name
PL/SQL Block;
Statement Triggers: Example
CREATE OR REPLACE TRIGGER chk_time
BEFORE INSERT OR UPDATE OR DELETE
ON emp
BEGIN
IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN'))
OR (TO_CHAR (SYSDATE, 'HH24') NOT BETWEEN '08' AND '18')
THEN
raise_application_error
(-20400,
'You can
not perform any DML Operation'
);
END IF;
END;
Row Triggers
The trigger body executes once for each row affected by the triggering event.
Syntax for creating Row Triggers:
CREATE [ OR REPLACE ] TRIGGER trigger_name
trigger_timing event1 [ OR event2 OR event3 ]
ON table_name
FOR EACH ROW
[WHEN condition]
PL/SQL Block;
FOR EACH ROW: Designates the trigger to be a row trigger
WHEN condition: Specifies the trigger restriction
For the UPDATE event there is an optional clause
[OF column_name[,column_name…..]]
Using OLD and NEW Qualifiers
In a ROW LEVEL trigger all the column values of the current row, before modification and after modification, are available to the trigger block as local variables. To access these values, the OLD and NEW quantifiers are used
e.g. :OLD.empno, :NEW.deptno
:old and :new variables: Example
CREATE TABLE emp_hist
( empno NUMBER(5),
oldsal NUMBER(10),
newsal NUMBER(10));
CREATE OR REPLACE TRIGGER log_trig
AFTER UPDATE OF sal
ON emp
FOR EACH ROW
BEGIN
IF :NEW.sal < :OLD.sal
THEN
raise_application_error (-20101, 'Salary cannot be decremented');
ELSE
INSERT INTO emp_hist
VALUES (:OLD.empno, :OLD.sal, :NEW.sal);
END IF;
END;
Sequence of Execution of Triggers
Multiple triggers can be created on the same table for the same event.
The order of execution is as follows
Execute all BEFORE STATEMENT triggers
For each row in the target table:
(a) Execute all BEFORE ROW triggers
(b) Execute the DML statement and perform integrity constraint checking
(c) Execute all AFTER ROW triggers
Execute all AFTER STATEMENT triggers
Sequence of Execution of Triggers: Example
CREATE OR REPLACE TRIGGER t1
BEFORE UPDATE
ON emp
BEGIN
DBMS_OUTPUT.put_line('This is statement level before update trigger');
END;
CREATE OR REPLACE TRIGGER t2
BEFORE UPDATE
ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('This is row level before update trigger');
END;
CREATE OR REPLACE TRIGGER t3
AFTER UPDATE
ON emp
FOR EACH ROW
BEGIN
DBMS_OUTPUT.put_line('This is row level after update trigger');
END;
CREATE OR REPLACE TRIGGER t4
AFTER UPDATE
ON emp
BEGIN
DBMS_OUTPUT.put_line('This is statement level after update trigger');
END;
Conditional Predicates
You can combine several triggering events in one trigger
To identify which event has raised the trigger, use conditional predicates:
INSERTING
UPDATING
DELETING
They return a TRUE value depending upon the DML statement executed
Conditional Predicates: Example
CREATE TABLE audit_table
(user_name varachar2(10),
table_name VARCHAR2(10),
ins NUMBER(4),
del NUMBER(4),
upd NUMBER(4));
INSERT INTO audit_table
VALUES (USER, 'EMP', 0, 0, 0);
CREATE OR REPLACE TRIGGER audit_emp
AFTER INSERT OR UPDATE OR DELETE
ON emp
FOR EACH ROW
BEGIN
IF INSERTING
THEN
UPDATE audit_table
SET ins = ins + 1
WHERE user_name = USER AND table_name = 'EMP';
ELSIF DELETING
THEN
UPDATE audit_table
SET del = del + 1
WHERE user_name = USER AND table_name = 'EMP';
ELSIF UPDATING
THEN
UPDATE audit_table
SET upd = upd + 1
WHERE user_name = USER AND table_name = 'EMP';
END IF;
END;
WHEN Clause
Valid for row triggers only
Trigger body executes for those rows that meet the condition
Evaluates for each row
OLD and NEW variables can be referenced here
For OLD and NEW variables we should not use colon (:) in the WHEN condition
CREATE OR REPLACE TRIGGER trig_when
AFTER UPDATE OF sal
ON emp
FOR EACH ROW
WHEN (OLD.sal > NEW.sal)
BEGIN
raise_application_error (-20009, 'Cannot reduce salary');
END;
Restrictions on a Trigger
By default, Transaction Control Language commands like COMMIT or ROLLBACK are not allowed within a trigger body
e. g.
CREATE TRIGGER trig
AFTER INSERT
ON emp
BEGIN
INSERT INTO emp_log
VALUES (SYSDATE, 'Insert on emp');
COMMIT;
END;
INSERT INTO emp(empno) VALUES (1);
INSERT INTO emp(empno) VALUES (1)
*
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.TAB1_TRIG", line 3
ORA-04088: error during execution of trigger 'SCOTT.TAB1_TRIG'
Use of TCL Commands in a Trigger
CREATE OR REPLACE TRIGGER tab1_trig
AFTER INSERT
ON tab1
DECLARE
-- declare the trigger as separate transaction from the
-- triggering event
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO emp_log
VALUES (SYSDATE, 'Insert on emp');
COMMIT; -- allowed only in autonomous triggers
END;
SQL> INSERT INTO emp(empno) VALUES (1);
1 row created.
Autonomous transactions:
Autonomous transactions execute separately from the current transaction.
Unlike regular triggers, autonomous triggers can contain COMMIT and ROLLBACK statements.
Managing Triggers
Disable / Enable trigger:
ALTER TRIGGER trigger_name ENABLE/DISABLE;
Disable / Enable all triggers for a table:
ALTER TABLE table_name DISABLE/ENABLE ALL TRIGGERS;
Dropping Triggers:
DROP TRIGGER trigger_name;
USER_TRIGGERS data dictionary view:
To query database trigger details stored in a database dictionary
Mutating Table Error
This error occurs when we create a row level trigger on a table that attempts to access the same table inside the trigger body
A Row-level trigger can not read from or write to the table, on which it is fired. However a statement level trigger can perform these actions on the table on which it is written
Mutating Table Error: Example
-- TRIGGER for checking that there is only 1 President in EMP
CREATE OR REPLACE TRIGGER checjjob
BEFORE INSERT OR UPDATE OF job
ON emp
FOR EACH ROW
WHEN (UPPER (NEW.job) = 'PRESIDENT')
DECLARE
CURSOR mycur
IS
SELECT empno, job
FROM emp;
BEGIN
FOR myvar IN mycur
LOOP
IF myvar.job = 'PRESIDENT'
THEN
raise_application_error (-20001, 'There can be only one president');
END IF;
END LOOP;
END;
Dealing with Mutating Table Error
Step 1: Create a package specification to contain a flag value that will be set by row level trigger and read by statement level trigger
-- package for declaring a global variable
CREATE OR REPLACE PACKAGE presidentpack
AS
flag NUMBER (1) := 0;
END presidentpack;
Step 2: Create an after row level trigger that sets the flag value to 1 when the new value of job is PRESIDENT
CREATE OR REPLACE TRIGGER trrowlevpresident
AFTER INSERT OR UPDATE OF job
ON emp
FOR EACH ROW
WHEN (UPPER (NEW.job) = 'PRESIDENT')
BEGIN
presidentpack.flag := 1;
END;
Step 3: Create an after statement level trigger that performs the check.
CREATE OR REPLACE TRIGGER trstatelevpresident
AFTER INSERT OR UPDATE OF job
ON emp
DECLARE
presidentcount NUMBER (2);
BEGIN
IF presidentpack.flag = 1
THEN
presidentPack.flag = 0; -- resetting the flag
SELECT COUNT (empno)
INTO presidentcount
FROM emp
WHERE UPPER (job) = 'PRESIDENT';
IF presidentcount > 1
THEN
raise_application_error
(-20001,
'Only one
president is allowed'
);
END IF;
END IF;
END;
Performance Impact of DML Triggers
The DML statements that initiate triggers execute slowly because they execute additional SQL statements, and the user might not know that other actions are occurring.
The execution time for a trigger event depends on the complexity of the trigger action and whether it initiates other triggers. The time increases as the number of cascaded triggers increases.
Also when a trigger is created, Oracle server has to use a background process to continuously monitor the trigger event
Greetings Mate,
ReplyDeleteThis is indeed great! But I think perhaps you are generally referring PL/SQL Triggers which is getting unsustainable.
I need some help from you experts on this stored procedure..
There are 4-5 batch procedures that will call this stored procedure. The batch will call the stored procedure with 'I' parameter in the beginning of the batch process. The batch will call the stored procedure with 'O' parameter at the end of the batch process. The stored procedure should write the details into the auditing table.
But great job man, do keep posted with the new updates.
Shukran,
Radhey