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
...









(7) Composite Data Types

[ Oracle PL/SQL ]
Composite data types: COllections and Records


Composite data type:
  • Stores values that have internal components (scalar or composite).
  • Collections can be built of any SQL or PL/SQL datatype.
  • Fall into two categories: arrays(size allocated at definition) and lists.
  • Lists are called associative arrays when they can be indexed by non-sequential numbers or unique strings.
  • Multidimensional collections can be built as PL/SQL datatypes. In this case, collection elements are record structures.

Collection:
  • Internal components always have the same data type (elements).
  • Access syntax: variable_name(index)
  • To create a collection variable:
    1. Define a collection type
    2. Create a variable of that type
    OR use %TYPE.

Collection Types:
  • Associative Arrays (former PL/SQL table or index-by table)
  • Variable-size Arrays (VARRAY)
  • Nested Table



Number of Elements: { Specified | Unspecified }
  • Whether you need to define the maximum size of the collection at declaration time.
  • Specified: VARRAY; Unspecified: Associative array; Nested table

Uninitialized status: { Empty | Null }
  • Empty: Exists, but has no elements. (Use EXTEND method to add).
  • Null: Does not exist. Must be initialized (Made empty or assigned a value).
  • Null: VARRAY, Nested Table. Empty: Associative Array

Scope: { Local | Public | Standalone (Schema) }
  • Local: Defined and available only within a PL/SQL block.
  • Public: Defined in a package specification.(ref: package_name.type_name)
  • Standalone: Defined at schema level.(ref: CREATE TYPE)


Collections: Associative Array (former PL/SQL TABLE)

  • Name introduced in Oracle 10g
  • Oracle 8 - 9i: index-by tables
  • Oracle 7: Pl/SQL tables
  • Can be indexed by Integer or String (no order here)
  • Can be passed as parameter and returned by function
  • Can be declared as a CONSTANT
  • The AA is only a PL/SQL datatype: can only be referred in a PL/SQL scope.
  • AAs are typically defined in package specifications when they are to be used externally from an anonymous or named block program.
  • AAs Numeric indexes do not need to be sequential.
  • These are sparsely populated structures: can have gaps in index sequences.
  • AAs are dynamically sized (like the NESTED TABLE datatype).

Who has the highest salary in each department?

(a) Define a CURSOR to extract:
[ Dept Name, Employee Name, Max Salary ]
(b) Define a RECORD TYPE to hold:
[ Employee Name, Max Salary ]
(c) Define an ASSOCIATIVE ARRAY TYPE (IS TABLE OF) indexed by String.
(d) Declares a variable of the ASSOCIATIVE ARRAY TYPE
(e) Open the CURSOR and populate the variable with data from.
(f) Print the data.

SQL> Set serveroutput on

SQL> DECLARE
   -- Declare cursor ccur to extract data (dname, ename, maxsal)
 Cursor ccur IS
  select d.department_name dname, 
         e.first_name ||' '|| e.last_name ename, e.salary maxsal
  from employees e, 
       ( select department_id, max(salary) msal
        from employees
        group by department_id) s, 
       departments d
  where e.department_id = d.department_id
   and e.department_id = s.department_id
   and e.salary = s.msal
  order by e.department_id;
  
    -- Define a Record type to hold (ename, maxsal)
  TYPE EmpSalRec IS RECORD (ename varchar2(60), maxsal employees.salary%TYPE);

   -- Define an associative array indexed by department name. The array stores the
   -- the composite structure (ename, maxsal)
  TYPE MaxSalDep IS TABLE OF EmpSalRec INDEX BY departments.department_name%TYPE;
  
   -- Declare a variable of the associative array type.
  v_MaxSalDep MaxSalDep;
  
BEGIN
 FOR deps IN ccur LOOP
    v_MaxSalDep(deps.dname).ename := deps.ename;
    v_MaxSalDep(deps.dname).maxsal := deps.maxsal;
    dbms_output.put_line('MaxSal Dept '      || deps.dname || ': ' || 
                          ' (' || v_MaxSalDep(deps.dname).ename || 
                          ', ' || v_MaxSalDep(deps.dname).maxsal || ');'
                         );
 END LOOP;
END;
/

MaxSal Dept Administration:  (Mary Fansom, 25000);
MaxSal Dept Marketing:  (Michael Hartstein, 13000);
MaxSal Dept Purchasing:  (Den Raphaely, 11000);
MaxSal Dept Human Resources:  (Kimberely Grant, 7000);
MaxSal Dept Shipping:  (Adam Fripp, 8200);
MaxSal Dept IT:  (Alexander Hunold, 9000);
MaxSal Dept Public Relations:  (John Fergunson, 26000);
MaxSal Dept Sales:  (John Russell, 14000);
MaxSal Dept Executive:  (Steven King, 24000);
MaxSal Dept Finance:  (Nancy Greenberg, 12000);
MaxSal Dept Accounting:  (Shelley Higgins, 12000);

Using Associative Array as a Constant
  • The example below loads an Associative Array Constant with the names of all departments.
  • Using the AA Constant, it then lists the number of employees in each department.
  • The AA Constant will be defined with the scope of a package.

(a) In the Package Spec, define the specification of the AA Constant TYPE and the specification of the Function that will initialize the AA Constant.
(b) In the Package Body, write the initialization function.
(c) In an anonymous block, declare constant, intialize it, and loop through the array.

SQL> Set serveroutput on

SQL> CREATE OR REPLACE PACKAGE My_Types AUTHID DEFINER IS
   -- Declaring an AA in a package spec makes it available for invoked and invoking 
   -- subprograms and to anonymous blocks.
  TYPE AA_FixDept IS TABLE OF departments.department_name%TYPE
                    INDEX BY pls_integer;
  FUNCTION Init_AA_FixDept RETURN AA_FixDept;
END My_Types;
/
Package created.

SQL> CREATE OR REPLACE PACKAGE BODY My_Types IS
    -- Function Init_AA_FixDept will initialize the AA Constant.
  FUNCTION Init_AA_FixDept RETURN AA_FixDept IS    
    CURSOR ccur IS
      SELECT department_name FROM departments;
    Ret AA_FixDept;
    i PLS_INTEGER := 0;
    BEGIN    
     FOR j IN ccur LOOP
      Ret(i) := j.department_name;
       -- Use dbms_output to check the proper loading of the AA Constant.
       -- Comment or remove the line later.
      dbms_output.put_line( 'Loading dept '|| i || ': ' || j.department_name);
      i := i + 1;
     END LOOP;
   RETURN Ret;   
  END Init_AA_FixDept;
END My_Types;
/
Package created.

SQL> Set serveroutput on
SQL> DECLARE
   -- Declare the AA Constan c_AAdept. 
   -- The Constant should be initialized at declaration time.
 c_AAdept CONSTANT My_Types.AA_FixDept := My_Types.Init_AA_FixDept();
 CURSOR cnumep (namedep departments.department_name%TYPE) IS
    SELECT COUNT(*) 
    FROM employees e, departments d
    WHERE e.department_id = d.department_id
    and department_name = namedep;
 v_index integer;
 v_numep integer;
BEGIN
  -- Listing the number of employees in each department;
  -- Loop through each component of the AA Constant and pass the department name
  -- as parameter to the explicit cursor cnumep.
  v_index := c_AAdept.First;
  LOOP
   OPEN cnumep(c_AAdept(v_index));
   FETCH cnumep INTO v_numep;
   dbms_output.put_line('Dept ' || c_AAdept(v_index) || ' has ' || 
                        v_numep || ' employees');
   CLOSE cnumep;
   EXIT WHEN v_index = c_AAdept.Last;
   v_index := c_AAdept.next(v_index);
  END LOOP;
END;
/

(Edited ouput):
Loading dept 0: Administration
Loading dept 1: Marketing
(...)
Loading dept 26: Payroll

Dept Administration has 2 employees
Dept Marketing has 2 employees
Dept Purchasing has 6 employees
(...)
Dept Payroll has 0 employees

When to use what?

Appropriate uses for AAs:
- The physical size is unknown and
- the type will not be used in tables.
- Good for standard solutions (i.e. using maps and sets).
- Good for small lookup tables.

When passing collections App <==> DB server. Use either:
VARRAYs
- You know the maximum number of elements.
- You usually access the elements sequentially.
- The physical size of the collection is static
- The collection may be used in tables.
- Varrays are the closest thing to arrays in other programming languages (Java, C, C++, C#).

NESTED TABLES
- The physical size is unknown and the type may be used in tables.
- Nested tables are like lists and bags in other programming languages.





Collections: VARRAYS
  • Introduced in Oracle 8
  • Can be used in table, record, and object definitions.
  • Fixed max size at creation.
  • Num elements: Between 0 and declared maximum size.
  • May be stored in permanent tables and accessed by SQL.
  • If VARRAY variable is less than 4 KB: it resides inside the table of which it is a column; otherwise, it resides outside the table but in the same tablespace.
  • Must be initialized. Otherwise is a null collection.
  • Densely populated structures: they do not allow gaps in the index values.
  • VARRAY and NESTED TABLE datatypes are structures indexed by sequential integers.

Using Varray: loading values from table
  • Load and print name and hire_date of the three longest employeed workers

(a) Define a Varray of STRING with max 3 rows.
(b) Declare the varray variable. Use constructor to set it to null.
(c) Declare CURSOR to return list of data from database.
(d-f) Print empty varray. Load Varray looping through cursor. Print loaded Varray.

SQL> set serveroutput on
DECLARE
-- Define a varray with a maximum of 3 rows.
TYPE str_varray IS VARRAY(3) OF VARCHAR2(100);

-- Declare the varray with and initialize it with null values.
-- Failure to do so at this point will produce an 
-- "ORA-06531: Reference to uninitialized collection".

-- Memory and proper index values are allocated opon initialization of EACH ELEMENT.
-- If you fail to initialize ALL elements, you need to use the EXTEND method or 
-- you'll face an "ORA-06533: Subscript beyond count" at run-time.
varray_str str_varray := str_varray(NULL,NULL,NULL);

vstr varchar2(100);
i PLS_INTEGER :=1;
CURSOR ccur IS
  select ename, hire_date
  from (select first_name || last_name as ename, hire_date, 
                  row_number() over (order by hire_date) r
          from employees)
  where r between 1 and 3;

BEGIN
-- Print initialized null values.
dbms_output.put_line('Varray initialized as nulls.');
dbms_output.put_line('––––––––––––––--------------');
FOR i IN 1..3 LOOP
 dbms_output.put_line ('String Varray ['||i||'] ' ||
                                '['||varray_str(i)||']';
END LOOP;

-- Assign values to subscripted members of the varray.
FOR j in ccur LOOP
 varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
 i := i + 1;
END LOOP;
-- Print initialized null values.
dbms_output.put (CHR(10));  -- Visual line break.
dbms_output.put_line('Varray initialized as values.');
dbms_output.put_line('-----------------------------');

FOR j IN 1..3 LOOP
 dbms_output.put_line('String Varray ['||j||'] ' ||
                               '['||varray_str(j)||']');
END LOOP;
END;
/

anonymous block completed
Varray initialized as nulls.
––––––––––––––--------------
String Varray [1] []
String Varray [2] []
String Varray [3] []

Varray initialized as values.
-----------------------------
String Varray [1] [StevenKing 17-JUN-87]
String Varray [2] [JenniferWhalen 17-SEP-87]
String Varray [3] [NeenaKochhar 21-SEP-89]

SQL> CREATE OR REPLACE TYPE str_varray AS 
                       VARRAY(3) of VARCHAR2(100);
/

Type created.

DECLARE
-- initialize without allocating space
varray_str str_varray := str_varray(); 
(...)
BEGIN
FOR j in ccur LOOP
 -- increment index and allocate space
 varray_str.EXTEND;
 varray_str(i) := j.ename || ' ' || to_char(j.hire_date);
 i := i + 1;
END LOOP;
(...)
END;

Alternatively:
  1. You could creat a user-defined object type.
  2. Benefits: it may be referenced from any programs that have permission to use it, whereas a PL/SQL varray type structure is limited to the program unit.
  3. You could allocate space for the VARRAY
    at run-time, as you increment the index:




Collections: Nested Tables
  • Introduced in Oracle 8
  • Initially defined as densely populated arrays, but may become sparsely populated as records are deleted.
  • May be stored in permanent tables and accessed by SQL.
  • May be dynamically extended: act more like bags and sets than arrays.
  • May contain a list of one or more compound datatypes (PL/SQL records) when they work exclusively in a PL/SQL scope.
  • In the database, a nested table column type stores an unspecified # of rows in no particular order.
  • When you retrieve a nested table value from the database into a PL/SQL nested table variable, PL/SQL gives the rows consecutive indexes, starting at 1.

(1) Define a composite NESTED TABLE TYPE (IS TABLE OF) of the type of employees table
(2) Create variable of the defined type. Initialize empty.
(3) Read all employees records on Cursor
(4) Iteract through the cursor and
(4.1) Extend the nested table
(4.2) Load three fields of the nested table with data from cursor.

set serveroutput on
DECLARE
 TYPE emp_table IS TABLE OF employees%ROWTYPE; 
 emps emp_table := emp_table();
 CURSOR ccur IS
  select employee_id, first_name, salary
  from employees;
 j PLS_INTEGER;
BEGIN
-- Print initialized null values.
dbms_output.put_line('Nested table initialized empty.');
dbms_output.put_line('-------------------------------');
j := 1;
dbms_output.put_line('Begin loading...');
FOR i IN ccur LOOP
 emps.extend;
 emps(j).employee_id := i.employee_id;
 emps(j).first_name := i.first_name;
 emps(j).salary := i.salary;
dbms_output.put ('Nested Table Extended ['||j||'] ');
dbms_output.put_line('['||emps(j).employee_id ||', '
      || emps(j).first_name || ', ' || emps(j).salary ||']'); 
j := j + 1;
END LOOP;
END;
/

anonymous block completed
Nested table initialized empty.
-------------------------------
Begin loading...
Nested Table Extended [1] [100, Steven, 24000]
Nested Table Extended [2] [101, Neena, 17000]
Nested Table Extended [3] [102, Lex, 17000]
(...)
Nested Table Extended [108] [210, John, 26000]
Nested Table Extended [109] [211, Mary, 25000]

Collection Methods
Method Type Description
DELETE Procedure Deletes elements from collection.
TRIM Procedure Deletes elements from end of varray or nested table.
EXTEND Procedure Adds elements to end of varray or nested table.
EXISTS Function Returns TRUE if and only if specified element of varray or nested table exists.
FIRST Function Returns first index in collection.
LAST Function Returns last index in collection.
COUNT Function Returns number of elements in collection.
LIMIT Function Returns maximum number of elements that collection can have.
PRIOR Function Returns index that precedes specified index.
NEXT Function Returns index that succeeds specified index.

Using collection methods
set serveroutput on 
declare 
 type nbrlast is table of varchar2(10);
  n nbrlast := nbrlast('paul','john','ringo','george');
begin
 dbms_output.put_line('Num elements held: ' || n.count);
 dbms_output.put_line('The last element is ' || n(n.last));
 n.extend;     -- append a new (null) element
 n(n.last) := 'Yoko';
 dbms_output.put_line('Added Yoko');
 dbms_output.put_line('Now, num elements held: ' || n.count);
 dbms_output.put_line('The last element now is ' || n(n.last));
 n.extend(3);  -- append two null elements to the collection
 dbms_output.put_line('appended three null elements');
 dbms_output.put_line('Now, num elements held: ' || n.count);
 n.delete(6);
 dbms_output.put_line('deleted element 6');
 dbms_output.put_line('Now, num elements held (count): ' || n.count);
 dbms_output.put_line('Now, highest subscription # (last): ' || n.last);
 n(7) := 'Ghost';
 dbms_output.put_line('element 7 is now: ' || n(7));
 dbms_output.put_line('Now, num elements: ' || n.count);

 if n.limit is null then
   dbms_output.put_line('Max num elements: unlimited.');
 end if;
 
 for i in n.first..n.last loop
   if n.exists(i) then 
     if n(i) IS NULL then
        dbms_output.put_line('Element #  '|| i || ' is NULL');
     else
        dbms_output.put_line('Element #  '|| i || ' is '|| n(i));
     end if;
   else
        dbms_output.put_line('Element #  '|| i || ' does not exist');
   end if;
  end loop;
end;
/

anonymous block completed
Num elements held: 4
The last element is george
Added Yoko
Now, num elements held: 5
The last element now is Yoko
appended three null elements
Now, num elements held: 8
deleted element 6
Now, num elements held (count): 7
Now, highest subscription # (last): 8
element 7 is now: Ghost
Now, num elements: 7
Max num elements: unlimited.
Element #  1 is paul
Element #  2 is john
Element #  3 is ringo
Element #  4 is george
Element #  5 is Yoko
Element #  6 does not exist
Element #  7 is Ghost
Element #  8 is NULL

















(ref) INSTEAD OF triggers

NON-UPDATABLE Views contain any:
  • One or more Table Joins.
  • A GROUP BY clause.
  • START WITH ...CONNECT BY.
  • DISTINCT.
  • Aggregate function (i.e. SUM, AVG, MIN, MAX, COUNT).
  • Set operators.
  • Derived columns (i.e. concatenated, pseudocolumns, etc).

INSTEAD OF triggers

INSTEAD OF triggers are created on
  • (usually) a non-updatable view
  • a nested table column of a non-updatable view

What?
  • INSTEAD OF trigger cannot be conditional.
  • Is the only way to update a view that is not inherently updatable
  • Is always a row-level trigger
  • Can read :OLD and :NEW, but cannot change them.
  • The timing events (BEFORE and AFTER) is not applicable for INSTEAD OF triggers.
  • INSTEAD OF trigger can fire for all three DML statements (I/U/D).
  • Prior to Oracle8.1.6 INSTEAD OF triggers functionality was included in the Enterprise Edition only.

Why use?
  • Used to intercept DML statements and replace them with alternative code.
  • Useful when application does not need (or should not) see the underlying tables (i.e. for security reasons). In this case:
    1. The application issues DMLs against the view
    2. The INSTEAD OF trigger intercepts and rewrite the DMLs to the underlying tables.
  • Useful also when the system uses object-relational functionality.
  • Updates against complex views may result in "ORA-01776-cannot modify more than one base table through a join view" or "ORA-01779-cannot modify a column which maps to a non key-preserved table"
How?
  • The database fires the INSTEAD OF trigger instead of running the triggering DML statement.
  • The trigger should: (a) determine what operation was intended and (b) perform the appropriate DML operations on the underlying tables.
Creation Syntax:
CREATE [OR REPLACE] TRIGGER trigger_name
 INSTEAD OF {dml_statement }
 ON {object_name | database | schema}
 FOR EACH ROW
 [DISABLE]
 [WHEN (logical_expression)]
 [DECLARE]
  declaration_statements;
BEGIN
  execution_statements;
END [trigger_name];
/
In the example below (taken from here) you:

(1) Create the base tables Custormer and Orders.
(2) Create a view order_info.
(3) Create a INSTEAD OF trigger order_info_insert that translates the DML statement into statements acting on the base tables (Orders and Customers).
(4) Insert data on the order_info view. Check the insertion on the base tables.

(1-2) Create base tables Customer, Orders and view.
SQL> CREATE TABLE customers ( 
     customer_id NUMBER (6)  NOT NULL  PRIMARY KEY, 
     cust_first_name VARCHAR2 (20)  NOT NULL , 
     cust_last_name VARCHAR2 (20)  NOT NULL , 
     cust_address VARCHAR2 (200) , 
     phone_numbers VARCHAR2 (12) , 
     nls_language VARCHAR2 (3) , 
     nls_territory VARCHAR2 (30) , 
     credit_limit NUMBER (9,2) , 
     cust_email VARCHAR2 (30) , 
     account_mgr_id NUMBER (6));

Table created.
SQL> CREATE TABLE orders ( 
     order_id NUMBER (12)  NOT NULL Primary key, 
     order_date TIMESTAMP  NOT NULL , 
     order_mode VARCHAR2 (8) , 
     customer_id NUMBER (6)  NOT NULL , 
     order_status NUMBER (2) , 
     order_total NUMBER (8,2) , 
     sales_rep_id NUMBER (6) , 
     promotion_id NUMBER (6));

Table created.
SQL> CREATE OR REPLACE VIEW order_info AS
   SELECT c.customer_id, c.cust_last_name, c.cust_first_name,
          o.order_id, o.order_date, o.order_status
   FROM customers c, orders o
   WHERE c.customer_id = o.customer_id;

View created.
SQL>

Now, if you try to perform an INSERT in the view order_info, it will fail with an ORA-01779 error:

SQL>INSERT INTO order_info 
    values (345, 'Rogers', 'John', 1250, sysdate, 1);

Error: ORA-01779: "cannot modify a column which maps to a non key-preserved table"
*Cause:    An attempt was made to insert or update columns of a join view which
           map to a non-key-preserved table.
*Action:   Modify the underlying base tables directly.


A Key preserved table means that the row from the base table will appear AT MOST ONCE in the output view on that table. Here one customer clearly may have multiple orders, so customer_id will most probably appear multiple times on order_info.

So, if you issue an INSERT against order_info, how can the database decide what to do with it?
Should it insert into orders AND customers? Only into orders? Or only into customers?

Well, you need to write an instead of trigger to intercept the DML against the trigger and issue DMLs against the base table(s) according to your business rules..

In this example, the trigger will split the INSERT coming from the application and insert on each table accordingly:


(3) Create a INSTEAD OF trigger order_info_insert that translates the DML statement into statements acting on the base tables (Orders and Customers).
SQL> CREATE OR REPLACE TRIGGER order_info_insert
  INSTEAD OF INSERT ON order_info
DECLARE
 duplicate_info EXCEPTION;
 PRAGMA EXCEPTION_INIT (duplicate_info, -00001);
BEGIN
 INSERT INTO Customers
   (customer_id, cust_last_name, cust_first_name)
 VALUES (:new.customer_id, :new.cust_last_name, :new.cust_first_name);
 INSERT INTO Orders (order_id, order_date, customer_id, order_status)
 VALUES (:new.order_id, :new.order_date, :new.customer_id, :new.order_status);
EXCEPTION
 WHEN duplicate_info THEN
   raise_application_error (
     num=> -20107,
     msg=> 'Duplicate customer or order ID');
END order_info_insert;
/
Trigger created.
SQL>

(4) Insert data on the order_info view. Check the insertion on the base tables.
SQL> INSERT into order_info 
 (Customer_id, cust_last_name, cust_first_name, order_id, order_date, order_status)
VALUES
 (1, 'Smith', 'John', 2500, '13-AUG-09', 0);
1 rows inserted.


SQL> Select Customer_id, cust_last_name, cust_first_name from customers;

CUSTOMER_ID  CUST_FIRST_NAME  CUST_LAST_NAME       
------------ ---------------- ----------------- 
           1 John             Smith 

SQL> Select order_id, order_date, order_status from orders;

ORDER_ID    ORDER_DATE                              ORDER_STATUS
---------- ---------------------------------------- ------------------------
     2500  13-AUG-09 12.00.00.000000 AM             0

SQL>

INSTEAD OF triggers on Nested Table Column of View


Nested Table Column of View (?)

You can create a view that has a nested table as one of its columns.
Now if you want to be able to update such a view, you need a instead of trigger.
It will intercept the object-relational INSERT and translate it to one that inserts into the base table.



(1) To create a view with a nested table as one of its columns you can:

(a) create the object type for the elements of the nested table. In this case, the type nte captures attributes of employees.

CREATE OR REPLACE TYPE nte AUTHID DEFINER IS
 OBJECT (
   emp_id   number(6),
   lastname varchar2(25),
   job      varchar2(10),
   sal      number(8,2));


(b) create the object table type. Here it will define a table of objects of the type nte.

CREATE OR REPLACE TYPE emp_list_ IS TABLE OF nte;

(c) Now you define the view containing the nested table in one of its columns (emplist).

CREATE OR REPLACE VIEW dept_view AS
  SELECT d.department_id,
  d.department_name,
  CAST (MULTISET (SELECT e.employee_id, e.last_name, e.job_id, i.salary
                  FROM employees e
                  WHERE e.department_id = d.department_id) 
                  AS emp_list_
                 ) emplist
  FROM departments d; 

(d) You can select the contents of emplist, and compare with a direct select of the base table:

SQL> select emplist from dept_view where department_id=10;

EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
-----------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4400)


SQL> SELECT employee_id, last_name, job_id, salary
     FROM employees
     WHERE department_id = 10;

EMPLOYEE_ID LAST_NAME		      JOB_ID	     SALARY
----------- ------------------------- ---------- ----------
200 Whalen		      AD_ASST	       4400

(e) Now, if you try to insert directly into the dept_view, you'll receive an ORA-25015.

SQL> INSERT into table (
                        select d.emplist
                        from dept_view d
                        where department_id = 10
     ) 
     VALUES (1001, 'Glenn', 'AC_MGR', 10000);

SQL Error: ORA-25015: cannot perform DML on this nested table view column
*Cause:    DML cannot be performed on a nested table view column except through
an INSTEAD OF trigger
*Action:   Create an INSTEAD OF trigger over the nested table view column
and then perform the DML.

(f) So you need an INSTEAD OF trigger:

CREATE OR REPLACE TRIGGER dept_emplist_tr
 INSTEAD OF INSERT ON NESTED TABLE emplist OF dept_view
 REFERENCING NEW as Employee
             PARENT as Department
 FOR EACH ROW
BEGIN
  -- Intercept the insert into the nested table.
  -- Translate it to an insert into the base table.
  INSERT INTO employees (
              employee_id, last_name, email, hire_date, job_id, salary, department_id)
  VALUES (
     :Employee.emp_id,
     :Employee.lastname,
     :Employee..lastname || '@company.com', 
     SYSDATE,
     :Employee.job,
     :Employee.sal,
     :Department.department_id);
END;


(g) Now you if repeat the insert statement, the trigger will fire and perform the insert in the base table employees:

SQL> INSERT into table (
                        select d.emplist
                        from dept_view d
                        where department_id = 10
     ) 
     VALUES (1001, 'Glenn', 'AC_MGR', 10000);

1 rows inserted.

(h) You can check the results by querying the dept_view view and employees table:

SQL> select emplist from dept_view where department_id = 10;

EMPLIST(EMP_ID, LASTNAME, JOB, SAL)
--------------------------------------------------------------------------------
EMP_LIST_(NTE(200, 'Whalen', 'AD_ASST', 4400), NTE(1001, 'Glenn', 'AC_MGR', 10000))


SQL> SELECT employee_id, last_name, job_id, salary
     FROM employees
     WHERE department_id = 10;

EMPLOYEE_ID LAST_NAME		      JOB_ID	     SALARY
----------- ------------------------- ---------- ----------
	200 Whalen		      AD_ASST	       4400
       1001 Glenn		      AC_MGR	      10000

2 rows selected.



(ref) Trigger on Object Table

You can create triggers on object tables. In this case, the trigger can reference the pseudocolumn OBJECT_VALUE.
In the example below you:

(1) Create an OBJECT_TYPE to define a table.
(2) Create an OBJECT TABLE obj_employee
(3) Insert data on obj_employee
(4) Create a table emp_hist for logging updates to obj_employee
(5) Create a trigger emp_obj_trg.
  • The trigger will execute for each row of obj_employee affected by a DML statement.
  • The old and new values of the object emp in obj_employee will be written in emp_history.
  • The old and new values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE


(1-3) Create OBJECT_TYPE, OBJECT TABLE and Insert data.

SQL> CREATE or REPLACE TYPE EmployeeType AS OBJECT (
  Id             number,
  first_name     varchar2(15),
  last_name      varchar2(15),
  hire_date      date,
  salary         number(8,2)
  );

Type created.
SQL> CREATE TABLE obj_employee OF EmployeeType;

Table created.
SQL> INSERT INTO obj_employee VALUES (
    EmployeeType(1, 'John', 'Martic', '02-AUG-03', 95000))
1 row created.

SQL> INSERT INTO obj_employee VALUES (
    EmployeeType(2, 'Mary', 'Kempft', '02-JUL-99', 98000)))
1 row created.

SQL> INSERT INTO obj_employee VALUES (
    EmployeeType(3, 'Greg', 'Bloom', '02-AUG-09', 55000))
1 row created.

SQL> commit;
Commit complete.

SQL> select * from obj_employee;

    ID FIRST_NAME      LAST_NAME       HIRE_DATE    SALARY
------ --------------- --------------- ------------ ----------
     1 John            Martic          02-AUG-03         95000
     2 Mary            Kempft          02-JUL-99         98000
     3 Greg            Bloom           02-AUG-09         55000



(4) Create history table emp_hist:
SQL> CREATE TABLE emp_hist 
  (dt          date,
   username    varchar2(20),
   old_obj_emp EmployeeType,
   new_obj_emp EmployeeType);

table created.
SQL>

Compare emp_hist and obj_employee:

  • obj_employee is an object table. Each of its rows is an object of the type EmployeeType
  • emp_hist is a traditional table. Two of its columns are objects of the type EmployeeType
(?) How is the translation/mapping OO-Relational done?
SQL> desc obj_employee
 Name               Null?  Type
 ------------------ ------ ------------------
 ID                         NUMBER
 FIRST_NAME                 VARCHAR2(15)
 LAST_NAME                  VARCHAR2(15)
 HIRE_DATE                  DATE
 SALARY                     NUMBER(8,2)

SQL> desc emp_hist
 Name               Null?  Type
 ------------------ ------ ------------------
 DT                        DATE
 USERNAME                  VARCHAR2(20)
 OLD_OBJ_EMP               EMPLOYEETYPE
 NEW_OBJ_EMP               EMPLOYEETYPE


(5) Create a trigger emp_obj_trg.
  • The trigger will execute for each row of obj_employee affected by a DML statement.
  • The old and new values of the object emp in obj_employee will be written in emp_history.
  • The old and new values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE

SQL> CREATE OR REPLACE TRIGGER emp_obj_trg
 AFTER UPDATE on obj_employee
 FOR EACH ROW
BEGIN
 DBMS_OUTPUT.PUT_LINE('Logging Update');
 INSERT INTO emp_hist (dt, username, old_obj_emp, new_obj_emp)
 VALUES (sysdate, user, :OLD.OBJECT_VALUE, :NEW.OBJECT_VALUE);
END emp_obj_trg;

Trigger created.
SQL> 

SQL> set serveroutput on 
SQL> update obj_employee set salary=salary*1.10 where id=1;
Logging Update

1 row updated.

SQL> update obj_employee set salary=salary*.98;
Logging Update
Logging Update
Logging Update

3 rows updated.


(6) Check the audit table (using implicit cursor):

SQL> set serveroutput on
SQL> BEGIN
 for j in (select * from emp_hist) loop
  dbms_output.put_line (
   'Date: ' || j.dt ||'-- User '|| j.username);
   DBMS_OUTPUT.PUT_LINE(
   '(old rec)-- ID:'|| j.old_obj_emp.id || '-- OLD SALARY: '|| j.old_obj_emp.salary);
  DBMS_OUTPUT.PUT_LINE(
   '(new rec)-- ID:'|| j.new_obj_emp.id || '-- NEW SALARY: '|| j.new_obj_emp.salary);
 end loop;
end;
/

Date: 22-AUG-11-- User FHAT
(old rec)-- ID:1-- OLD SALARY: 95000
(new rec)-- ID:1-- NEW SALARY: 104500
Date: 22-AUG-11-- User FHAT
(old rec)-- ID:1-- OLD SALARY: 104500
(new rec)-- ID:1-- NEW SALARY: 102410
Date: 22-AUG-11-- User FHAT
(old rec)-- ID:2-- OLD SALARY: 98000
(new rec)-- ID:2-- NEW SALARY: 96040
Date: 22-AUG-11-- User FHAT
(old rec)-- ID:3-- OLD SALARY: 55000
(new rec)-- ID:3-- NEW SALARY: 53900

PL/SQL procedure successfully completed.
SQL>








(17) On Triggers



Introduction
  • Named PL/SQL unit. Can be enabled or disabled, but cannot be explicitly invoked.
  • Trigger is created on or defined on the item (to which it will be "attached"): table, view, schema or database.
  • Firing criteria is based on a triggering event (DML, DDL, System) and on a timing specification (before, after, instead of). A conditional clause (WHEN) may also be used to further specify the triggering rules.
  • Triggers do not accept arguments.
  • Triggers can be written in PL/SQL or JAVA.
  • Starting on Oracle 11g, triggers can now be created in the disabled state.

Triggers: what for?
Customization of database management; centralization of some business or validation rules; logging and audit.
  • Overcome the mutating-table error.
  • Maintain referential integrity between parent and child.
  • Generate calculated column values
  • Log events (connections, user actions, table updates, etc)
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views
  • Enforce referential integrity when child and parent tables are on different nodes of a distributed database
  • Publish information about database events, user events, and SQL statements to subscribing applications
  • Enforce complex security authorizations: (i.e. prevent DML operations on a table after regular business hours)
  • Prevent invalid transactions
  • Enforce complex business or referential integrity rules that you cannot define with constraints
  • Control the behavior of DDL statements, as by altering, creating, or renaming objects
  • when they change data in a view>
  • Audit information of system access and behavior by creating transparent logs
(but, however, nonetheless, take heed:),
  • if the trigger code turns out to be very(?) long, you will more likely have better performance using a stored procedure instead. In fact, a trigger cannot be larger than 32Kb (because stored on LONG column). If you need to write something longer, use a stored procedure instead.
  • You can’t control the sequence of or synchronize calls to triggers, and this can present problems if you rely too heavily on triggers
  • A trigger can call a SQL statement that in turn fires another trigger: The number of cascading triggers is limited to 32, after which an exception is thrown. (11g and earlier)

Triggers: How much is too much?
  • DML statements on tables with DML Triggers are likely to have decreased perform.
  • You may choose to disable triggers before loading data. Of course the cost to this is the work you'll have to perform latter what the disabled triggers did not do.
  • If the task is complex, you may spread it across multiple triggers. However, this will make it maintenance more difficult, since it is likely to make the entire process harder to follow later.
  • Triggers may get disabled by accident: For example, DDLs on objects touched by a trigger may render it unusable. If you don't catch this, you may end up with missing/corrupt data.


Five types of Triggers
DDL Triggers
  • On CREATE, ALTER, DROP
  • Useful to control or monitor DDL statements.
  • An instead-of create table trigger allow for:
    • Ensuring that table creation meets development standards (i.e. proper storage or partitioning clauses.
    • Monitor poor programming practices (i.e. programs create and drop temporary tables rather than use Oracle collections. Temporary tables can fragmentdisk space and degrade database performance over time.
DML Triggers
  • Statement-level or row-level
  • Audit, check, save, and replace values before they are changed.
  • Automatic numbering of numeric primary keys (through row-level trigger).
Compound Triggers
  • Act as statement- and row-level triggers.
  • Lets you capture information at four timing points:
    (a) before the firing statement;
    (b) before each row change from the firing statement;
    (c) after each row change from the firing statement; and
    (d) after the firing statement.
  • Audit, check, save, and replace values before they are changed when you need to take action at both the statement and row event levels.
Instead-of Triggers
  • Enable you to stop performance of a DML statement and redirect the DML statement.
  • Often used to manage how you write to non-updatable views: They apply business rules and directly insert,update, or delete rows in tables that define updatable views.
  • Alternatively, they insert, update, or delete rows in tables unrelated to the view.
System Database event Triggers
  • Fire when a system activity occurs in the database (i.e. logon and logoff).
  • Useful for auditing information of system access. (You can track system events and map them to users).


Constraints
  • Apply to OLD and NEW data.
  • Easier to write and less error-prone.
Triggers
  • Apply only to NEW data.
  • Can enforce complex business rules.
  • Enforce ref integrity on distributed databases.


Using DML triggers

Using Compound triggers (11g only)

Using triggers on object tables
You can create triggers on object tables. In this case, the trigger can reference the pseudocolumn OBJECT_VALUE. Check an example here.

Using INSTEAD OF triggers

INSTEAD OF triggers are created on views. This allow DML statements to be issued against non-updatable views. Check an example here.

Privileges required to use Triggers

- CREATE TRIGGER: For your own objects.
- CREATE ANY TRIGGER + ALTER ANY TABLE.
- EXECUTE:to fire triggers on other schemas

Trigger Design Guidelines
  • Do not create triggers that duplicate database features. For example, do not create a trigger to reject invalid data if you can do the same with constraints.
  • Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).

    For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.
  • If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETE statement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. To detect this situation, include a counter variable in the package.
  • Do not create recursive triggers. The trigger fires recursively until it runs out of memory.
  • If you create a trigger that includes a statement that accesses a remote database, then put the exception handler for that statement in a stored subprogram and invoke the subprogram from the trigger.
  • Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.
  • Only committed triggers fire. A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds.

Trigger Restrictions
  • Maximum Trigger Size
    • Max 32Kb. If needed, you can move code into functions, procedures or packages. In this case, the code could also be reused. stored modules can also be wrapped.
    • If the logic for your trigger requires much more than 60 lines of PL/SQL source text, then put most of the source text in a stored subprogram and invoke the subprogram from the trigger
  • DCL and DDL Restrictions
    • Only an autonomous trigger can run TCL or DDL statements
    • Nonsystem trigger bodies can’t contain DDL statements. They also can’t contain Transaction Control Language (TCL) commands, like ROLLBACK, SAVEPOINT,or COMMIT.
    • A trigger cannot invoke a subprogram that runs transaction control statements, because the subprogram runs in the context of the trigger body.
    • If you declare a trigger as autonomous, nonsystem trigger bodies can contain Data Control Language commands because they don’t alter the transaction scope.
    • To enable a trigger to work outside the scope of a triggering statement you use include in its DECLARE block: PRAGMA AUTONOMOUS_TRANSACTION;
    • A larger problem with SQL statements exists with remote transactions. If you call a remote
      schema-level function or procedure from a trigger body, it is possible that you may encounter a
      timestamp or signature mismatch. A mismatch invalidates the trigger and causes the triggering
      SQL statement to fail.
  • LONG and LONG RAW Datatypes
    • The LONG and LONG RAW datatypes are legacy components. Migrate out of them.
    • A trigger cannot declare a variable of the LONG or LONG RAW data type.
    • You may, however, insert into a LONG or LONG RAW column when the value can be converted CHAR or VARCHAR2.
    • Row-level triggers cannot use a :new,:old or parent with a LONG or LONG RAW column.
  • Triggers will fail if try to access a mutating table.
  • Oracle 11g has relaxed some mutating table restrictions

Triggers and data transfers
These utilities may fire triggers:
SQL*LOoader (sqlldr), Data Pump Import (impdp) and Original import (imp)
SQL*Loader (sqlldr):
- During a SQL*Loader conventional load, INSERT triggers fire.
- Before a SQL*Loader direct load, triggers are disabled.
Data Pump Import (impdp):
- If a table to be imported does not exist on the target database, or if you specify TABLE_EXISTS_ACTION=REPLACE, then impdp creates and loads the table before creating any triggers, so no triggers fire.
- If a table to be imported exists on the target database, and you specify either TABLE_EXISTS_ACTION=APPEND or TABLE_EXISTS_ACTION=TRUNCATE, then impdp loads rows into the existing table, and INSERT triggers created on the table fire.
Original Import (imp):
- If a table to be imported does not exist on the target database, then imp creates and loads the table before creating any triggers, so no triggers fire.
- If a table to be imported exists on the target database, then the Import IGNORE parameter determines whether triggers fire during import operations:
- If IGNORE=n (default), then imp does not change the table and no triggers fire.
- If IGNORE=y, then imp loads rows into the existing table, and INSERT triggers created on the table fire.