Oracle DML Triggers

DML Triggers

DML triggers:
  • Simple
  • Compound
    • can fire at one, some, or all of the preceding timing points
  • INSTEAD OF
    • Created on a noneditioning view, or on a nested table column of a noneditioning view.
    • Used to perform a DML action on the underlying tables of a view.
    • The database fires the INSTEAD OF trigger instead of running the triggering DML statement.
  • Crossedition
    • For use only in edition-based redefinition

  • Triggering event: DELETE, INSERT, and UPDATE.
  • Simple DML trigger fires at exactly one of these timing points:
  • Before the triggering statement runs (BEFORE trigger).
  • After the triggering statement runs (AFTER trigger).
    • Statement triggers are always executed, independently of whether the triggering event actually affected any rows in the underlying table.
    • Statement-level triggers are also known as table-level triggers.
    • You cannot use a WHEN clause in a statement-level trigger.
    • You also cannot reference the new or old pseudo-records in a statement-level trigger. Doing so results in a compile-time error (ORA-04082: NEW or OLD references not allowed in table level triggers).
  • Before each row that the triggering statement affects (BEFORE each row trigger).
  • After each row that the triggering statement affects (AFTER each row trigger).
    • If the DML statement does not affect any row the trigger is NOT executed.

DML Triggers and DCL (Commit, Rollback, Savepoint)
  • You cannot use SQL Data Control Language (DCL) in them, unless you declare the trigger as autonomous.
  • When triggers run inside the scope of a transaction, they disallow setting a SAVEPOINT or performing either a ROLLBACK or COMMIT statement.
  • Also, No function or procedure called by a trigger can have a DCL statement in their execution path.

DML Trigger: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
 {BEFORE | AFTER}
 {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
 ON table_name
 [FOR EACH ROW]
 [WHEN (logical_expression)]
[DECLARE]
  [PRAGMA AUTONOMOUS_TRANSACTION;]
  declaration_statements;
BEGIN
  execution_statements;
END [trigger_name];
/

Trigger firing sequence for any DML (I/U/D)
  1. Before DML FOR EACH STATEMENT.
  2. Before DML FOR EACH ROW.
  3. DML statement executed.
  4. After DML FOR EACH ROW.
  5. After DML FOR EACH STATEMENT.


(?) How many triggers can you have for INSERT on Employees?
  • No restrictions. You can create many for a single event.
  • However, the ORDER of firing is not guaranteed.
  • Consolidate triggers, if sequence in important.

Before UPDATE Statement-level Triggers
  • Useful to ensure integrity rules (i.e. new value should be within some range) or business requirements (i.e. ensure that user has required privileges for operation).
  • Can be also used to check whether the triggering event should be allowed to complete successfully. If the statement is found to be not authorized, the trigger could, for example, log it on an audit table, send notification messages, and raise an exception so that the statement fails.

SQL> CREATE or REPLACE TRIGGER audit_emp_hours
 BEFORE INSERT OR UPDATE OR DELETE 
 ON employees
BEGIN
  IF TO_CHAR (SYSDATE, 'HH24:MI') NOT BETWEEN '08:00' AND '18:00'
        OR TO_CHAR (SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
    RAISE_APPLICATION_ERROR (-20205,
		'You may only make changes during normal office hours');
  END IF;
END;
/
Trigger Created.
SQL> 

SQL> update employees set salary=43000 where employee_id=210;
update employees set salary=43000 where employee_id=210
         *
ERROR at line 1:
ORA-20205: You may only make changes during normal office hours
ORA-06512: at "HR.AUDIT_EMP_HOURS", line 4
ORA-04088: error during execution of trigger 'HR.AUDIT_EMP_HOURS'


Using CONDITIONAL PREDICATES to audit actions/events:
  • INSERING
  • UPDATING
  • DELETING
{ TRUE | FALSE }

The trigger below writes into an audit table (AUDITBL) when any DML statement is executed on the EMPLOYEES table.

(a) Create the Audit table (AUDITBL)

SQL> CREATE TABLE AudiTbl ( 
     id_audit NUMBER  NOT NULL , 
     username VARCHAR2 (20) , 
     action VARCHAR2 (400) , 
     dt_action DATE , 
     table_name VARCHAR2 (100) , 
     old_value VARCHAR2 (400) , 
     new_value VARCHAR2 (400) 
    );
Table created.

SQL>ALTER TABLE AudiTbl 
    ADD CONSTRAINT "AudiTblPK" PRIMARY KEY (id_audit);
Table altered.

SQL> CREATE SEQUENCE seq_audit START WITH 1;
Sequence created.
SQL> 

(b) Create Conditional AFTER UPDATE/INSERT/DELETE Trigger on (EMPLOYEES).

SQL> Set Serveroutput on;

SQL> CCREATE OR REPLACE TRIGGER audit_employees
 AFTER
    INSERT OR
    UPDATE OF salary OR
    UPDATE OF department_id OR
    DELETE
  ON employees
  FOR EACH ROW
BEGIN
  CASE
    WHEN INSERTING THEN
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'INSERT', sysdate, 'employees', null, null);
      DBMS_OUTPUT.PUT_LINE('Inserting on employees');
    WHEN UPDATING('salary') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE salary', sysdate, 'employees', 
        :OLD.salary, :NEW.salary);
    WHEN UPDATING('department_id') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'UPDATE Dept_id', sysdate, 'employees', 
        :OLD.department_id, :NEW.department_id);
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
      INSERT into AudiTbl VALUES
       (seq_audit.nextval, user, 'DELETE', sysdate, 'employees', 
        :OLD.employee_id, null);
  END CASE;
END;
/

Notes:
  • OLD, NEW and PARENT are correlation names. :OLD and :NEW qualifiers can only be used in row level triggers. These reference the old and new values of a column, respectively.







(c) Test the INSERT trigger on (EMPLOYEES).

SQL> insert into employees (employee_id, first_name, last_name, email, 
         phone_number, hire_date, job_id, salary, commission_pct, 
         manager_id, department_id)
values (employees_seq.nextval, 'John', 'Fergunson', 'email@email.com', 
        null, sysdate, 'IT_PROG', 20000, null, null, null);
Inserting on employees

1 row created.
SQL>
SQL> Select * from auditbl;

ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
1 JOHNF INSERT 18-AUG-10 employees    

(d) Test the UPDATE (salary) trigger

SQL> update employees set salary=salary*2 where employee_id=210;
Updating salary

1 row updated.
SQL>
SQL> Select * from auditbl;

ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
1 JOHNF INSERT 18-AUG-10 employees    
2 MARCJ UPDATE 11-Sep-10 employees 15000 30000



Row-Level Update Trigger
  • The trigger below is fired when an employee is assigned to the department 10.
  • It fires AFTER an UPDATE on emploees, but ONLY WHEN the update was on the department_id COLUMN AND the new value for that column is set to 10.
  • Note that in the WHEN clause you use NEW. In the trigger bodyyou use :NEW.
  • Inside a trigger body, you preface the pseudo-records with a colon (:). The colon let you reference the externally scoped pseudo-records in the trigger body.

SQL> CREATE OR REPLACE TRIGGER audit_dept10
  AFTER UPDATE OF department_id
  ON employees
  FOR EACH ROW
  WHEN (new.department_id = 10)
BEGIN
  DBMS_OUTPUT.PUT_LINE('Update on employees');
  INSERT into AudiTbl VALUES
    (seq_audit.nextval, user, 'UPDATE', sysdate, 'employees', 
        :old.department_id, :new.department_id);
END;
/

Trigger created.
SQL> set serveroutput on
SQL> update employees 
        set department_id = 10 
        where employee_id in (201, 202, 203, 210);

4 rows updated.
Update on employees
Update on employees
Update on employees
Update on employees

 
SQL> select * from auditbl;



ID_AUDIT USERNAME ACTION DT_ACTION TABLE_NAME OLD_VALUE NEW_VALUE
18 JAFFN UPDATE 26-AUG-11 employees 70 10
18 JAFFN UPDATE 26-AUG-11 employees 20 10
18 JAFFN UPDATE 26-AUG-11 employees 20 10
18 JAFFN UPDATE 26-AUG-11 employees 40 10


Exception handling in Triggers







(ref) Oracle DDL Triggers

DDL Triggers
Can be used to:
  • Monitor significant events in the database.
  • Monitor errant code that can that can corrupt or destabilize your database.
  • Use these in development, test, and stage systems to understand and monitor the dynamics of database activities.
  • Also useful when you patch your application code. They can let you find
    potential changes between releases.
  • During an upgrade: use instead-of create trigger to enforce table creation storage clauses or partitioning rules.
  • Track the creation and modification of tables by application programs that lead to database fragmentation.
  • Effective security tools: monitor GRANT and REVOKE privilege statements.

When can they fire? DDL Events
ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DDL (means: ANY DDL event), DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE

System-defined event attribute functions
ORA_CLIENT_IP_ADDRESS Returns the client IP address as a VARCHAR2.
DECLARE
 ip_address VARCHAR2(11);
BEGIN
 IF ora_sysevent = 'LOGON' THEN
    ip_address := ora_client_ip_address;
 END IF;
END;
ORA_DATABASE_NAME Returns the database name.
BEGIN
(...)
   db_name := ora_database_name;
(...)
END;
ORA_DES_ENCRYPTED_PASSWORD - Returns the DES-encrypted password as VARCHAR2.
- Equivalent to the value in the SYS.USER$ table PASSWORD col (11g).
- Passwds are no longer accessible in DBA_USERS or ALL_USERS
BEGIN
 IF ora_dict_obj_type = 'USER' THEN
   password := ora_des_encrypted_password;
 END IF;
END;
ORA_DICT_OBJ_NAME Returns the name of the object target of the DDL statement.
DECLARE
 database VARCHAR2(50);
BEGIN
 database := ora_obj_name;
END; 
ORA_DICT_OBJ_NAME_LIST The function returns the number of elements in the list as a PLS_INTEGER datatype. The name_list contains the list of object names touched by the triggering event.
DECLARE
  name_list DBMS_STANDARD.ORA_NAME_LIST_T;
  counter PLS_INTEGER;
BEGIN
  IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN
    counter := ora_dict_obj_name_list(name_list);
  END IF;
END;
ORA_DICT_OBJ_OWNER returns an owner of the object acted upon by the event.
database := ora_dict_obj_owner;
ORA_DICT_OBJ_OWNER_LIST formal parameter datatype is defined
in the DBMS_STANDARD package as ORA_NAME_LIST_T (table of varchar2(64)).
The function returns the number of elements in the list.
The owner_list contains the list of owners of objects
affected by the event.
DECLARE
  owner_list DBMS_STANDARD.ORA_NAME_LIST_T;
  counter PLS_INTEGER;
BEGIN
  IF ora_sysevent = 'ASSOCIATE_STATISTICS' THEN
     counter := ora_dict_obj_owner_list(owner_list);
  END IF;
END;
ORA_DICT_OBJ_TYPE
ORA_GRANTEE
ORA_INSTANCE_NUM
ORA_IS_ALTER_COLUMN
ORA_IS_CREATING_NESTED_TABLE
BEGIN
  IF ora_sysevent = 'CREATE' AND
    ora_dict_obj_type = 'TABLE' AND
    ora_is_creating_nested_table THEN
      INSERT INTO logging_table
      VALUES (ora_dict_obj_name||'.'||' 
            created with nested table.');
   END IF;
END;
ORA_IS_DROP_COLUMN
ORA_IS_SERVERERROR
ORA_LOGIN_USER>
ORA_PARTITION_POS returns the numeric position with the SQL text where you can insert a partition clause. This is only available in an INSTEAD OF CREATE trigger.
DECLARE
  sql_text ORA_NAME_LIST_T;
  sql_stmt VARCHAR2(32767);
  partition VARCHAR2(32767) := 'partitioning_clause';
BEGIN
  FOR i IN 1..ora_sql_txt(sql_text) LOOP
    sql_stmt := sql_stmt || sql_text(i);
  END LOOP;
  sql_stmt := SUBSTR(sql_text,1,ora_partition_pos – 1)
            ||' ' || partition||' '||
            SUBSTR(sql_test,ora_partition_pos);
  -- Add logic to prepend schema because 
  -- this runs under SYSTEM.
  sql_stmt := REPLACE(UPPER(sql_stmt),'CREATE TABLE '
            ,'CREATE TABLE '||ora_login_user||'.');
   EXECUTE IMMEDIATE sql_stmt;
END;
ORA_PRIVILEGE_LIST Returns list of privileges granted/revoked.
ORA_REVOKEE
ORA_SERVER_ERROR
ORA_SERVER_ERROR_DEPTH
ORA_SERVER_ERROR_MSG
ORA_SERVER_ERROR_NUM_PARAMS
ORA_SERVER_ERROR_PARAM
ORA_SQL_TXT Returns the substring of the processed SQL statement that triggered the event.
ORA_SYSEVENT
ORA_WITH_GRANT_OPTION
SPACE_ERROR_INFO

DDL Triggers: Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF} ddl_event ON {DATABASE | SCHEMA}
[WHEN (logical_expression)]
[DECLARE]
 declaration_statements;
BEGIN
 execution_statements;
END [trigger_name];
/



DDL Trigger on Creation statements

(a) create table audit_creation
(b) create sequence audit_creation_s1
(c) Create trigger audit_creation
(d) create a synonym to fire the trigger
(e) check the inserted row on audit_creation


CREATE TABLE audit_creation
( audit_creation_id NUMBER PRIMARY KEY,
  audit_owner_name VARCHAR2(30) NOT NULL,
  audit_obj_name   VARCHAR2(30) NOT NULL,
  audit_date       DATE NOT NULL);

CREATE SEQUENCE audit_creation_s1;



CREATE OR REPLACE TRIGGER audit_creation
 BEFORE CREATE ON SCHEMA
BEGIN
  insert into audit_creation values
  (audit_creation_s1.nextval, 
   ORA_DICT_OBJ_OWNER,
   ORA_DICT_OBJ_NAME,
   sysdate);
END audit_creation;

SQL> Create synonym empsym for hr.employees;

synonym created.
SQL> Select * from audit_creation;

AUDIT_CREATION_ID    AUDIT_OWNER_NAME       AUDIT_OBJ_NAME      AUDIT_DATE
-------------------  ---------------------- ------------------- ---------------
1                    HR                     EMPSYN              22-AUG-10 













(ref) PL/SQL Optimization

[ Oracle PL/SQL ]

Improving comunication: BULK SQL (FORALL and BULK COLLECT)

Topics

BULK SQL:
  • Minimizes overhead in PL/SQL <=> SQL comunication.
  • FORALL: Send statements in batches.
  • FORALL: faster than equivalent FOR LOOP.
  • FORALL: Can contain ONLY one DML statement.
  • SQL%BULK_ROWCOUNT: Is like an AA. keeps the # of rows affected by each DML in the FORALL statement.
  • SQL%BULK_EXCEPTIONS.[COUNT | ERROR_INDEX | ERROR_CODE]: Is like an AA. keeps information about EXCEPTIONS that occurred during the FORALL statement.

  • BULK COLLECT: Receive results in batches
  • Good when query/DML affects +4 database rows



FOR LOOP X FORALL
SQL> set serveroutput on
SQL> DECLARE
 TYPE NumList IS VARRAY(20) of number;
  -- depts to be deleted
 depts NumList := NumList(10,30,70); 
BEGIN
  FOR i IN depts.FIRST..depts.LAST LOOP
   dbms_output.put_line('deleting dept '||i);
   delete from emp_temp
   where department_id = depts(i);
   END LOOP;
END;
/
deleting dept 1
deleting dept 2
deleting dept 3 

PL/SQL procedure successfully completed.
SQL>

  • FORALL: Only one DML Statement.
  • Use SQL%BULK_ROWCOUNT to check # of affected rows.
SQL> set serveroutput on
SQL> DECLARE
 TYPE NumList IS VARRAY(20) of number;
 depts NumList := NumList(10,30,70);
BEGIN
  FORALL i IN depts.FIRST..depts.LAST
    delete from emp_temp
    where department_id = depts(i);

  -- How many rows were affected by
  -- each DELETE statement?
  FOR i IN depts.FIRST..depts.LAST LOOP
   DBMS_OUTPUT.PUT_LINE('Iteration #' 
     || i || ' deleted ' ||
     SQL%BULK_ROWCOUNT(i) ||'rows.');
  END LOOP;
END;
/
Iteration #1 deleted 1 rows.
Iteration #2 deleted 2 rows.
Iteration #3 deleted 5 rows.

PL/SQL procedure successfully completed.
SQL>


Comparing INSERT performance: FOR LOOP X FORALL
set serveroutput on
drop table parts1;
create table parts1 (pnum integer, pname varchar2(15));
drop table parts2;
create table parts2 (pnum integer, pname varchar2(15));

DECLARE
  TYPE NumTab IS TABLE OF parts1.pnum%type INDEX BY PLS_INTEGER;
  TYPE NameTab IS TABLE OF parts1.pname%type INDEX BY PLS_INTEGER;

  pnums NumTab;
  pnames NameTab;
  iterations CONSTANT PLS_INTEGER := 50000;
  t1 INTEGER;
  t2 INTEGER;
  t3 INTEGER;

BEGIN
 --populate collection
 FOR j IN 1..iterations LOOP
   pnums(j)  := j;
   pnames(j) := 'Part No. ' || to_char(j);
 END LOOP;

 -- get t1 before start FOR LOOP
 t1 := dbms_utility.get_time;
 
 FOR i IN 1..iterations LOOP
   insert into parts1 (pnum, pname)
   values (pnums(i), pnames(i));
 END LOOP;

 -- get t2 before start FORALL 
 t2 := dbms_utility.get_time;

 FORALL i IN 1..iterations
   insert into parts2(pnum, pname)
   values (pnums(i), pnames(i));

 t3 := dbms_utility.get_time;

 dbms_output.put_line('Execution Time (secs)');
 dbms_output.put_line('---------------------');
 dbms_output.put_line('FOR LOOP: ' || to_char((t2-t1)/100));
 dbms_output.put_line('FORALL:  ' || to_char((t3-t2)/100));
END;
/
anonymous block completed
Execution Time (secs)
---------------------
FOR LOOP: 1.74
FORALL:  .06

PLS-00436: Implementation restriction - Cannot reference RECORD fields within a FORALL
(...)
DECLARE
  TYPE PartsRec IS RECORD (pnum parts1.pnum%type, name parts1.pname%type);
  TYPE PartsRecTab IS TABLE OF PartsRec INDEX BY PLS_INTEGER;
  precs PartsRecTab;
  (...)
BEGIN
 --populate collection
 FOR j IN 1..iterations LOOP
   precs(j).pnum  := j;
   precs(j).pname := 'Part No. ' || to_char(j);
 END LOOP;
 
 (...)

 FORALL i IN 1..iterations
   insert into parts2(pnum, pname)
   values (precs(i).pnum, precs(i).pname);
 (...)
END;
/

Error report:
ORA-06550: line 31, column 12:
PLS-00436: implementation restriction: cannot reference fields of BULK In-BIND 
                                                              table of records





Example 12-10 FORALL Statement for Subset of Collection


Exception Handling


Bulk Statements: BULK COLLECT


  • BULK COLLECT: use the BULK COLLECT: statement with SELECT statements
    • Can be used (a) inside a SQL statement (implicit cursor) or (b) as part of a FETCH statement (explicit cursor).
    • With FETCH: you can append the LIMIT clause to set the maximum number of rows read from the cursor at a time.
    BULK COLLECT Syntax:
    DECLARE
    -- on a SQL Statement:
    SELECT column1 [, column2 [,...]]
    COLLECT BULK INTO collection1 [, collection2 [,...]]
    FROM
    table_name
    [WHERE where_clause_statements];
    
    -- with explicit cursor:
    FETCH cursor_name [(parameter1 [, parameter2 [,...]])]
    BULK COLLECT INTO collection1 [, collection2 [,...]]
    [LIMIT rows_to_return];
    
  • The BULK COLLECT INTO statement is much faster than a standard cursor because it has one parse, execute, and fetch cycle.
  • Scalar collections are the only supported SQL collection datatypes: No composite collections (record collections) (??).
  • FORALL: Use the FORALL: statement to INSERT, UPDATE or DELETE large data sets.
  • SQL%BULK_ROWCOUNT(i): Keeps the # of rows affected by each DML in the FORALL statement.
  • SQL%BULK_EXCEPTIONS(i).[COUNT | ERROR_INDEX | ERROR_CODE]: Keeps information about EXCEPTIONS that occurred during the FORALL statement.


set serveroutput on  
declare
  -- Using BULK COLLECT with scalar collections
  -- Here you will have to ensure that the discrete collections 
  -- remain synchronized
  type empid_c is table of employees.employee_id%type;
  type empdep_c is table of employees.department_id%type;
  type empsal_c is table of employees.salary%type;
  
  empid_ empid_c;
  empdep_ empdep_c;
  empsal_ empsal_c;
begin 
  -- selecting into the three collections, in parallel.
  -- (Q) why would you do such a thing???
  -- (A) The typical reason to opt for parallel collections is 
  --           to move the data from PL/SQL to external
  --           programming languages or web applications.
  
  select employee_id, department_id, salary
  bulk collect into empid_, empdep_, empsal_
  from employees;
  
  for i in 1..empid_.count loop
    dbms_output.put_line('id: ' || empid_(i) ||
                         ' earns: '|| empsal_(i));
  end loop;
end;

anonymous block completed
id: 100 earns: 24000
id: 101 earns: 17000
...


-- Alternativelly, you may use structured collections. 
-- However, these cannot be shared with external programs.
set serveroutput on  
declare
  -- define one list of records. Synchronization is given.
  type emprec is record(empid employees.employee_id%type, 
                        dept employees.department_id%type,
                        sal  employees.salary%type);                     
  type emptab is table of emprec;
  emptab_ emptab;

begin 
  -- collect directly into the structured table
  select employee_id, department_id, salary
  bulk collect into emptab_
  from employees;
  
  for i in 1..emptab_.count loop
    dbms_output.put_line('id: ' || emptab_(i).empid ||
                         ' earns: '|| emptab_(i).sal);
  end loop;
end;
anonymous block completed
id: 100 earns: 24000
id: 101 earns: 17000
...



-- Now using an explicit cursor instead of SQL Stmt
-- Here the BULK COLLECT is used on the FETCH statement.
set serveroutput on  
declare
  -- As before, (why?), using parallel scalar collections.
  -- remember they will need to be pretty well synchronized..
  type empid_c is table of employees.employee_id%type;
  type empdep_c is table of employees.department_id%type;
  type empsal_c is table of employees.salary%type;
  
  empid_ empid_c;
  empdep_ empdep_c;
  empsal_ empsal_c;

  -- here define the explicit cursor c1
  cursor c1 is 
   select employee_id, department_id, salary
   from employees;
  j pls_integer := 0; 
   
begin 
  open c1;
  loop
    -- now when you fetch the cursor records,
    -- use the BULK COLLECT clause and you may LIMIT the
    -- number of rows fetched...
    fetch c1 bulk collect into empid_, empdep_, empsal_ limit 10;
    exit when empid_.count = 0;
    j := j + 1;
    dbms_output.put_line('Fetch # ' || j);
    for i in 1..empid_.count loop
       dbms_output.put_line('id: ' || empid_(i) ||
                         ' earns: '|| empsal_(i));
    end loop;
  end loop;
end;

anonymous block completed
Fetch # 1
id: 100 earns: 24000
...
Fetch # 2
id: 110 earns: 8200
...
Fetch # 3
id: 120 earns: 8000
...