09 - Creating Compound, DDL Database Triggers

Before vs After Triggers

Table required for test

CREATE TABLE employees(

emp_id varchar2(50) NOT NULL PRIMARY KEY,

name varchar2(50) NOT NULL,

salary number NOT NULL

);

Example 1 - Before insert trigger which modifies the values being inserted

create or replace trigger konverto

before insert on employees

for each row

begin

dbms_output.put_line('inside trigger');

:new.salary :=100;

end;

Try to remove the for each row clause.

Discuss the result.

Try to change the timing when the trigger fires from before to after .

Discuss the result.

Example 2 - Before update trigger which modifies the values being modified

create or replace trigger konverto2

before update on employees

for each row

begin

dbms_output.put_line('inside trigger');

:new.salary :=300;

end;

Exercice - Before triggers - try it your self

Example table

CREATE TABLE orders

( order_id number(5),

quantity number(4),

cost_per_item number(6,2),

total_cost number(8,2),

updated_date date,

updated_by varchar2(10)

);

Write a trigger which modifies the update_date column to by sysdate whenever the orders table is updated.

Solution

CREATE OR REPLACE TRIGGER orders_before_update

BEFORE UPDATE

ON orders

FOR EACH ROW

DECLARE

v_username varchar2(10);

BEGIN

-- Find username of person performing UPDATE on the table

SELECT user INTO v_username

FROM dual;

-- Update updated_date field to current system date

:new.updated_date := sysdate;

-- Update updated_by field to the username of the person performing the UPDATE

:new.updated_by := v_username;

END;

Compound DML Triggers

In Oracle 11g, the concept of compound trigger was introduced. A compound trigger is a single trigger on a table that enables you to specify actions for each of four timing points:

• 1. Before the firing statement

  2. Before each row that the firing statement affects

  3. After each row that the firing statement affects

  4. After the firing statement

With the compound trigger, both the statement-level and row-level action can be put up in a single trigger. Plus there is an added advantage: it allows sharing of common state between all the trigger-points using variable. This is because compound trigger in oracle 11g has a declarative section where one can declare variable to be used within trigger. This common state is established at the start of triggering statement and is destroyed after completion of trigger

When to use Compound Triggers

The compound trigger is useful when you want to accumulate facts that characterize the “for each row” changes and then act on them as a body at “after statement” time. Two popular reasons to use compound trigger are:

• 1. To accumulate rows for bulk-insertion. We will later see an example for this.

  2. To avoid the infamous ORA-04091: mutating-table error.

Syntax

CREATE OR REPLACE TRIGGER compound_trigger_name

FOR [INSERT|DELETE]UPDATE [OF column] ON table

COMPOUND TRIGGER

-- Declarative Section (optional)

-- Variables declared here have firing-statement duration.

--Executed before DML statement

BEFORE STATEMENT IS

BEGIN

NULL;

END BEFORE STATEMENT;

--Executed before each row change- :NEW, :OLD are available

BEFORE EACH ROW IS

BEGIN

NULL;

END BEFORE EACH ROW;

--Executed aftereach row change- :NEW, :OLD are available

AFTER EACH ROW IS

BEGIN

NULL;

END AFTER EACH ROW;

--Executed after DML statement

AFTER STATEMENT IS

BEGIN

NULL;

END AFTER STATEMENT;

END compound_trigger_name;

Compound Triggers Rules

1. A compound trigger must be a DML trigger.

2. A compound trigger must be defined on either a table or a view.

3. OLD, :NEW, and :PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.

4. Only the BEFORE EACH ROW section can change the value of :NEW

Example: Using Compound Triggers in Table Auditing

Lets create a compound trigger for auditing a large table called ‘employees’. Any changes made in any field of ‘employees’ table needs to be logged in as a separate row in audit table ‘aud_empl’.

Since each row update in employees table needs to make multiple inserts in the audit table, we should consider using a compound trigger so that batching of inserts can be performed.

But before that we need to create our Tables:

--Target Table

CREATE TABLE employees(

emp_id varchar2(50) NOT NULL PRIMARY KEY,

name varchar2(50) NOT NULL,

salary number NOT NULL

);

Audit table

CREATE TABLE "AUD_EMP" ( "UPD_BY" VARCHAR2(50 BYTE) NOT NULL ENABLE, "UPD_DT" DATE NOT NULL ENABLE, "FIELD" VARCHAR2(50 BYTE) NOT NULL ENABLE, "FROM_VALUE" VARCHAR2(50 BYTE) NOT NULL ENABLE, "TO_VALUE" VARCHAR2(50 BYTE) NOT NULL ENABLE, "EMP_ID" NUMBER, "ACTION" VARCHAR2(20 BYTE) ) ;

Now the trigger…

On update of each row instead of performing an insert operation for each field, we store (buffer) the required attributes in a Arrays of type aud_emp. Once a threshold is reached (say 1000 records), we flush the buffered data into audit table and reset the counter for further buffering.

And at last, as part of AFTER STATEMENT we flush any remaining data left in buffer.

--Trigger

CREATE OR REPLACE TRIGGER aud_emp

FOR INSERT OR UPDATE

ON employees

COMPOUND TRIGGER

TYPE t_emp_changes IS TABLE OF aud_emp%ROWTYPE INDEX BY SIMPLE_INTEGER;

v_emp_changes t_emp_changes;

v_index SIMPLE_INTEGER := 0;

v_threshhold CONSTANT SIMPLE_INTEGER := 1000; --maximum number of rows to write in one go.

v_user VARCHAR2(50); --logged in user

PROCEDURE flush_logs

IS

v_updates CONSTANT SIMPLE_INTEGER := v_emp_changes.count();

BEGIN

FORALL v_count IN 1..v_updates

INSERT INTO aud_emp

VALUES v_emp_changes(v_count);

v_emp_changes.delete();

v_index := 0; --resetting threshold for next bulk-insert.

END flush_logs;

AFTER EACH ROW

IS

BEGIN

IF INSERTING THEN

v_index := v_index + 1;

v_emp_changes(v_index).upd_dt := SYSDATE;

v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER');

v_emp_changes(v_index).emp_id := :NEW.emp_id;

v_emp_changes(v_index).action := 'Create';

v_emp_changes(v_index).field := '*';

v_emp_changes(v_index).from_value := 'NULL';

v_emp_changes(v_index).to_value := '*';

ELSIF UPDATING THEN

IF ( (:OLD.EMP_ID <> :NEW.EMP_ID)

OR (:OLD.EMP_ID IS NULL AND :NEW.EMP_ID IS NOT NULL)

OR (:OLD.EMP_ID IS NOT NULL AND :NEW.EMP_ID IS NULL)

)

THEN

v_index := v_index + 1;

v_emp_changes(v_index).upd_dt := SYSDATE;

v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER');

v_emp_changes(v_index).emp_id := :NEW.emp_id;

v_emp_changes(v_index).field := 'EMP_ID';

v_emp_changes(v_index).from_value := to_char(:OLD.EMP_ID);

v_emp_changes(v_index).to_value := to_char(:NEW.EMP_ID);

v_emp_changes(v_index).action := 'Update';

END IF;

IF ( (:OLD.NAME <> :NEW.NAME)

OR (:OLD.NAME IS NULL AND :NEW.NAME IS NOT NULL)

OR (:OLD.NAME IS NOT NULL AND :NEW.NAME IS NULL)

)

THEN

v_index := v_index + 1;

v_emp_changes(v_index).upd_dt := SYSDATE;

v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER');

v_emp_changes(v_index).emp_id := :NEW.emp_id;

v_emp_changes(v_index).field := 'NAME';

v_emp_changes(v_index).from_value := to_char(:OLD.NAME);

v_emp_changes(v_index).to_value := to_char(:NEW.NAME);

v_emp_changes(v_index).action := 'Update';

END IF;

IF ( (:OLD.SALARY <> :NEW.SALARY)

OR (:OLD.SALARY IS NULL AND :NEW.SALARY IS NOT NULL)

OR (:OLD.SALARY IS NOT NULL AND :NEW.SALARY IS NULL)

)

THEN

v_index := v_index + 1;

v_emp_changes(v_index).upd_dt := SYSDATE;

v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER');

v_emp_changes(v_index).emp_id := :NEW.emp_id;

v_emp_changes(v_index).field := 'SALARY';

v_emp_changes(v_index).from_value := to_char(:OLD.SALARY);

v_emp_changes(v_index).to_value := to_char(:NEW.SALARY);

v_emp_changes(v_index).action := 'Update';

END IF;

END IF;

IF v_index >= v_threshhold THEN

flush_logs();

END IF;

END AFTER EACH ROW;

-- AFTER STATEMENT Section:

AFTER STATEMENT IS

BEGIN

flush_logs();

END AFTER STATEMENT;

END aud_emp;

/

Trigger efect

INSERT INTO employees VALUES (1, 'emp1', 10000);

INSERT INTO employees VALUES (2, 'emp2', 20000);

INSERT INTO employees VALUES (3, 'emp3', 16000);

UPDATE employees

SET salary = 2000

WHERE salary > 15000;

SELECT * FROM aud_emp;

Result:

Now any changes in any field of employees will to be written in aud_emp table. A beauty of this approach is we were able to access same data ‘v_emp_changes’ between statement and row triggering events.

With this in mind, one can see that it make sense to move v_emp_changes(v_index).upd_by := SYS_CONTEXT ('USERENV', 'SESSION_USER'); inside declarative(or BEFORE STATEMENT if complex computation) section as a pre-processing step. To do so, v_user variable declared in trigger body can be used and assigned value of logged in user in the declarative section itself. So that same computation is not made during after-each-row section, and is computed and stored in a variable just once before row-level execution begins.

DDL triggers

Using the Data Definition Language (DDL) triggers, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

DDL triggers execute every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger

DDLTrigger

AFTER DDL ON DATABASE

BEGIN

insert into

perfstat.stats$ddl_log

(

user_name,

ddl_date,

ddl_type,

object_type,

owner,

object_name

)

VALUES

(

ora_login_user,

sysdate,

ora_sysevent,

ora_dict_obj_type,

ora_dict_obj_owner,

ora_dict_obj_name

);

END;

/