Tuning Database Triggers

Tuning Database Triggers  : This topic addresses how to put triggers to more effective use.

Move Trigger Code to Stored Procedures
Database triggers are not held in a compiled form until PL/SQL 2.3 (Oracle7.3). As a consequence, it is more efficient to move trigger logic to a stored procedure and simply call the procedure from the trigger. This may mean passing all necessary trigger information (NEW and OLD column values, DML statement type, etc.) to the procedure because it does not have access to the information.

Create or Replace Trigger Update_Sum_T1
After Update OR INSERT OR DELETE
On EMP For Each Row
BEGIN
 Update_Sum_Prc (:OLD.salary, :NEW.Salary, :NEW.rowid);
END;

Do Not Use Triggers
The most efficient trigger is no trigger at all. Often triggers are incorrectly used to initialize default table columns, perform simple data integrity checking, or enforce referential integrity rules. All of these trigger components can now be replaced by DDL table constraints and referential integrity. These actions operate below the user level (internal to the RDBMS kernel) and are always more efficient than row-level triggers. For example, this example:

Create or Replace Trigger Validate_EMP_T1 on EMP
After  Insert On EMP For Each Row
DECLARE
 CURSOR C1 IS
 SELECT 'x'
 FROM   dept
 WHERE  dept_no = :NEW.dept_no;
 --
 xdummy VARCHAR2(1);
BEGIN
 IF :NEW.salary <= 0 THEN
  RAISE form_application_error ( -20000,
   'Salary must be greater than $0.00');
 END IF;
 --
 IF :NEW.salary > 100,000
 AND :NEW.sal_grade < 10 THEN
  RAISE form_application_error ( -20001,
   'Salary cannot excede 100,000 for theis Job Grade');
 END IF;
 --
 OPEN C1;
 FETCH C1 INTO xdummy;
 IF C1%notfound THEN
  RAISE form_application_error ( -20002,
  'Invalid Department Number Entered');
 END IF;
 --
 :NEW.last_upd_date := sysdate;
 :NEW.last_upd_user := user;
END;

would be better coded as

CREATE TABLE EMP
(
emp_no  NUMBER(6) NOT NULL,
....
dept_no  NUMBER(4)  CONSTRAINT EMP_dept_FK
     REFERENCES dept(deptno),
salary  NUMBER(12,2)   CHECK (salary > 0),
sal_grade NUMBER(2),
last_upd_date DATE   DEFAULT SYSDATE NOT NULL,
last_upd_user VARCHAR2(10)  DEFAULT USER NOT NULL,
CONSTRAINT check_sal
    CHECK (salary < 100000 OR sal_grade >= 10)
)
Note that DDL DEFAULT values column definitions can often be confusing because default functionality changes depending on how the row column is inserted into the table. We have always assumed that default values would be applied if no column values (NULL) were provided on initial row insertion. But it turns out that not referencing the column during row creation and inserting it with a value of null are two separate things. For example:

Table Tab1
(fld1  VARCHAR2(20),
 fld2  VARCHAR2(10)  DEFAULT USER )
 INSERT INTO tab1        values  ('ABC', NULL);
 INSERT INTO tab1 (fld1) values  ('ABC');
  Fld1          Fld2
  ------------  -------------
  ABC           NULL            <-- Default did not activate
  XYZ           SCOTT           <-- Default did activate
Exploit the WHEN Clause
Oracle has always boasted that DDL-defined referential integrity, check constraints, and default column settings are more efficient than application-level coding. Oracle claims that this built-in functionality operates one level below any user-defined SQL code (whatever that means). These claims are quite true, and the WHEN clause associated with a table trigger is no exception.

Always use the WHEN clause before you resort to inline trigger coding. With versions before Oracle7.3 (triggers are precompiled in Oracle7.3), this is even more important. More often than not, the first line of the trigger code is of the form

IF :NEW.column1 = 'A' THEN
For every row updated, this trigger is to be compiled and executed, and yet the very first line of PL/SQL code excludes the trigger processing. All this expensive processing can be avoided by a well-written WHEN clause. Remember that WHEN clause has access to everything to which a trigger has access, including full SQL functionality. The following example:

Create or Replace Trigger Validate_EMP_T1 on EMP
After Insert  OR Update
On EMP For Each Row
BEGIN
 IF UPDATING THEN
  IF :NEW.salary   !=  :OLD.salary
  OR :NEW.dept_no  !=  :OLD.dept_no THEN
   .
  END IF;
 ELSIF  INSERTING THEN
  IF :NEW.salary  != 0 THEN
   .
  END IF;
 END IF;
END;
would be better coded as

Create or Replace Trigger Validate_EMP_T1 on EMP
After Insert  OR Update
On EMP For Each Row
WHEN ( UPDATING  AND (  NEW.salary  != OLD.salary
                     OR NEW.dept_no != OLD.dept_no )
    OR INSERTING AND    NEW.salary  != 0             )
BEGIN
 IF UPDATING THEN
  .
 ELSIF  INSERTING THEN
  .
 END IF;
END;

Increase the Size of the Shared Pool
Because trigger code is repeatedly parsed and reparsed when it is executed multiple times, retaining the "parse tree" in memory helps performance. Monitor the activity within the shared buffer pool and increase the size of the pool (physical memory permitting) if you think that it is not retaining cursor information long enough.