(ref) System Event Triggers

[ Oracle PL/SQL ]

System Event Triggers

About:

• Created on either a schema or the database
• A SCHEMA trigger is created on a schema and fires whenever the user who owns it is the current user and initiates the triggering event.
• A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} database_event ON {database | schema}
[DECLARE]
declaration_statements;
BEGIN
execution_statements;
[EXCEPTION ...]
END [trigger_name];
/

Valid events are listed below.

• DDL Events: ALTER, ANALYZE, ASSOCIATE STATISTICS, AUDIT, COMMENT, CREATE, DISASSOCIATE STATISTICS, DROP, GRANT, NOAUDIT, RENAME, REVOKE, TRUNCATE, DDL
• Database Events: AFTER STARTUP, BEFORE SHUTDOWN, AFTER DB_ROLE_CHANGE, AFTER SERVERERROR, AFTER LOGON, BEFORE LOGOFF, AFTER SUSPEND.

Trigger on Schema
- The trigger fires to stop any drop of DB objects belonging to the HR schema. CREATE OR REPLACE TRIGGER stop_drop_tg BEFORE DROP ON hr.SCHEMA BEGIN RAISE_APPLICATION_ERROR ( num => -20000, msg => 'Cannot drop object'); END; /

Trigger on Database

- When user connects, the trigger runs the connecting procedure in the user_connecting package. - Before logoff the procedure disconnecting is executed. CREATE OR REPLACE TRIGGER connect_tg AFTER LOGON ON DATABASE BEGIN user_connection.connecting(sys.login_user); END; / CREATE OR REPLACE TRIGGER disconnect_tg BEFORE LOGOFF ON DATABASE BEGIN user_connection.disconnecting(sys.login_user); END; / Providing Fine-Grained Access Control As suggested in the examples above, you can also use LOGON triggers to run a package associated with an application context. An application context captures session-related information about the user who is logging in to the database. From there, your application can control how much access this user has, based on his or her session information. Note: If you have very specific logon requirements, such as preventing users from logging in from outside the firewall or after work hours, consider using Oracle Database Vault instead of LOGON triggers. With Oracle Database Vault, you can create custom rules to strictly control user access.

(21) Object Dependencies

[ Oracle PL/SQL ]

• If you alter the definition of a referenced object, dependent objects might not continue to function without error, depending on the type of alteration. For example, if you drop a table, no view based on the dropped table is usable.

[ALL_ | DBA_ | USER_]DEPENDENCIES: describe dependencies among db objects

SQL> create or replace view highsal as
 select * from employees
 where salary >=100000;
 
SQL> create or replace view commissioned as
  select first_name, last_name, commission_pct
  from employees
  where commission_pct > 0.00;

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;

OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          VALID

SQL> alter table employees modify email varchar2(100);

SQL> select object_name, status 
from user_objects
where object_type = 'VIEW'
order by object_name;


OBJECT_NAME      STATUS
---------------- -------
COMMISSIONED     VALID
HIGHSAL          INVALID


SQL> select name, type, referenced_name, referenced_type, dependency_type
from user_dependencies
order by name;

NAME            TYPE          REFERENCED_NAME  REFERENCED_TYPE  DEPENDENCY_TYPE
-----------     ------------  ---------------  --------------   -------------
CIRCLE_AREA     PROCEDURE     DBMS_OUTPUT      SYNONYM          HARD
CIRCLE_AREA     PROCEDURE     MY_PKG           PACKAGE          HARD
...
COMMISSIONED    VIEW          EMPLOYEES        TABLE            HARD
HIGHSAL         VIEW          EMPLOYEES        TABLE            HARD
...
MY_PKG          PACKAGE BODY  MY_PKG           PACKAGE          HAD
MY_PKG          PACKAGE BODY  DBMS_DB_VERSION  PACKAGE          HARD
...
MY_PKG          PACKAGE       DBMS_DB_VERSION  PACKAGE          HARD

• Cascading invalidation: Indirect dependents can be invalidated by changes to the reference object that do not affect them.
• If a change to C invalidates B, it invalidates A (and all other direct and indirect dependents of B).
• Coarse-grained invalidation: DDL statement that changes a referenced object invalidates all of its dependents.

On 11g R1: Fine-grained invalidation

• Fine-grained invalidation: DDL statement that changes a referenced object invalidates only dependents for which:
1. Dependent relies on the attribute of the referenced object that the DDL statement changed.
2. The compiled metadata of the dependent is no longer correct for the changed referenced object.
3. For example, if view v selects columns c1 and c2 from table t, a DDL statement that changes only column c3 of t does not invalidate v.

Minimize object invalidation

(a) Add Items to End of Package
This preserves the entry point numbers of existing top-level package items, preventing their invalidation.

CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
END;

-- Adding an item to the end of pkg1, as follows, 
-- does not invalidate dependents that reference the get_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE set_var (v VARCHAR2);
END;

-- Inserting an item between the get_var function and the set_var procedure
-- invalidates dependents that reference the set_var function:
CREATE OR REPLACE PACKAGE pkg1 IS
  FUNCTION get_var RETURN VARCHAR2;
  PROCEDURE assert_var (v VARCHAR2);
  PROCEDURE set_var (v VARCHAR2);
END;

(b) Reference Each Table Through a View
Reference tables indirectly, using views, enabling you to:

• Add columns to the table without invalidating dependent views or dependent PL/SQL objects.
• Modify or delete columns not referenced by the view without invalidating dependent objects
• The statement CREATE OR REPLACE VIEW does not invalidate an existing view or its dependents if the new ROWTYPE matches the old ROWTYPE.

Revalidation

To recompile a schema object using the appropriate SQL statement with the COMPILE clause.

• ALTER PACKAGE pkg1 COMPILE REUSE SETTINGS;

Requisites to use UTL_RECOMP package:

• This package must be run using SQL*PLUS.
• You must be connected AS SYSDBA to run this script.
• The following packages should have VALID status:
• STANDARD (standard.sql)
• DBMS_STANDARD (dbmsstdx.sql)
• DBMS_JOB (dbmsjob.sql)
• DBMS_RANDOM (dbmsrand.sql)
• No other DDL must be running on the database, or deadlock may result.
• This package uses the job queue for parallel recompilation.

You can use UTL_RECOMP package to recompile one or more invalid objects:

• This is useful after an application upgrade or patch, when is good practice to revalidate invalid objects to avoid application latencies that result from on-demand object revalidation.

-- Recompile all objects sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL();

-- Recompile objects in schema SCOTT sequentially:
EXECUTE UTL_RECOMP.RECOMP_SERIAL('SCOTT');

-- Recompile all objects using 4 parallel threads:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(4);

-- Recompile objects in schema JOE using the number of threads specified in the parameter JOB_QUEUE_PROCESSES:
EXECUTE UTL_RECOMP.RECOMP_PARALLEL(NULL, 'JOE');


Revalidate individual invalid objects using DBMS_UTILITY.VALIDATE.
-- Revalidate the procedure UPDATE_SALARY in schema HR:
begin
dbms_utility.validate('HR', 'UPDATE_SALARY', namespace=>1);
end;

-- Revalidate the package body HR.ACCT_MGMT:
begin
dbms_utility.validate('HR', 'ACCT_MGMT', namespace=>2);
end;


(ref) On Compound Triggers

Compound Triggers

About Compound triggers:

• First appeared in Oracle 11g.
• Can fire at multiple timing points.
• Each timing point section has its own executable parte and optional exection-handling part.
• Act as both statement- and row-level triggers.
• Can be defined on either a table or a view
• Do not support filtering actions with the WHEN clause or the use of the autonomous transaction PRAGMA
• The firing order of compound triggers is not guaranteed because they can be interleaved (mixed between) with the firing of standalone triggers.
• There is a declaration section for the trigger as a whole. Individual timing points share this single global declaration section, whose state is maintained for the lifetime of the statement. In addition, each timing point section has its own local declaration section.
• Compound triggers do not support an EXCEPTION block, but you can implement EXCEPTION
  blocks in any of the subordinate timing point blocks.
• Compound triggers do not fire when (a) the DML statement doesn’t change any rows and (b) the trigger does not implement at least a BEFORE STATEMENT or AFTER STATEMENT block.
• In previous releases this type of functionality was only possible by defining multiple triggers whose code and global variables were defined in a separate package.

Common uses of Compound DML Triggers:

• Accumulate rows - with values obtained or generated at each "EACH ROW" firing - so that these can be inserted with BULK-INSERT
• To avoid the mutating-table error (ORA-04091)

Restrictions:

• OLD, NEW, and PARENT cannot appear in the declarative part, the BEFORE STATEMENT section, or the AFTER STATEMENT section.
• Only the BEFORE EACH ROW section can change the value of NEW.
• A timing-point section cannot handle exceptions raised in another timing-point section.
• If a timing-point section includes a GOTO statement, the target of the GOTO statement must be in the same timing-point section.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
 FOR {INSERT | UPDATE | UPDATE OF column1 [, column2 [, column(n+1)]] | DELETE}
 ON table_name
 COMPOUND TRIGGER
[BEFORE STATEMENT IS
  [declaration_statement;]
BEGIN
  execution_statement;
END BEFORE STATEMENT;]
[BEFORE EACH ROW IS
  [declaration_statement;]
BEGIN
  execution_statement;
END BEFORE EACH ROW;]
[AFTER EACH ROW IS
  [declaration_statement;]
BEGIN
  execution_statement;
END AFTER EACH ROW;]
[AFTER STATEMENT IS
  [declaration_statement;]
BEGIN
  execution_statement;
END AFTER STATEMENT;]
END [trigger_name];
/

Mutating-Table error: what is it?

• A mutating table is a table that is being modified by a DML statement (i.e. by the effects of a DELETE CASCADE constraint).
• If a trigger is fired as the result of a DML statement in table A, it cannot query or modify table A (the table that the triggering statement is modifying.)
• When a row-level trigger encounters a mutating table, ORA-04091 occurs, the effects of the trigger and triggering statement are rolled back, and control returns to the user or application that issued the triggering statement.

Example:

(a) create table ELOG to log updates in the table EMPLOYEES
(b) create trigger to insert into ELOG the values (:OLD) that are modified when an UPDATE is issued against EMPLOYEES.
(c) The trigger should (1) Insert the OLD values into ELOG and (2) Print a statement with the number of rows remaining on EMPLOYEES.

• For this, it has to perform a SELECT in the table that is being modified (EMPLOYEES). And there the bicho pega...

create table elog (
  emp_id number(6),
  l_name varchar2(25),
  f_name varchar2(20));

create or replace trigger log_deletions
  after delete on employees
  for each row
declare
 n integer;
begin
 insert into log values
   (:old.employee_id,
    :old.last_name, 
    :old.first_name);
 select count(*) into n from employees;
 dbms_output.put_line('there are now ' || n || ' employees.');
end;

delete from employees where employee_id=197;

SQL Error: ORA-04091: table EMPLOYEES is mutating, trigger/function may not see it
ORA-06512: at "LOG_DELETIONS", line 8
ORA-04088: error during execution of trigger 'LOG_DELETIONS'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.

(?) How to solve this error?

(1) You may Use a temporary table
Instead of using one AFTER each row trigger that updates the mutating table, use two triggers
(a) an AFTER each row trigger that updates the temporary table and
(b) an AFTER statement trigger that updates the mutating table with the values from the temporary table.

(2) Use a compound DML trigger:

Compound Trigger: Bulk Logging updates to solve ORA-04091

The example below uses a compound trigger to log salary updates in the employees table, and capture old and new values for the updated columns.
(a) create table emp_log_update
(b) create sequence emp_log_update_seq
(c) Create trigger emp_log_tg
(d) Configure client information (use sys_context to set client info)
(e) Update rows on employees to fire the compound trigger
(f) check the inserted row on emp_log_update

-- create table to log updates. The table will be used by the compound trigger.
create table emp_log_update
 (emp_log_id       number,
  emp_id           number,
  oldval           varchar2(20),
  newval           varchar2(20),
  created_by       varchar2(20), 
  creation_date    date,
  last_updated_by  varchar2(20),
  last_update_date date);

create sequence emp_log_update_seq;

-- create compound trigger. 
-- The For each row section: stores :old and :new values for each updated row
-- The statement section: performs a bulk insert in the log table.

CREATE OR REPLACE TRIGGER employee_update_tg
 FOR UPDATE of SALARY on EMPLOYEES
 COMPOUND TRIGGER
 TYPE emp_rec IS RECORD
  (emp_log_id      emp_log_update.emp_log_id%type,
   emp_id          emp_log_update.emp_id%type,
   oldval          emp_log_update.oldval%type,
   newval          emp_log_update.newval%type,
   created_by      emp_log_update.created_by%type, 
   creation_date   emp_log_update.creation_date%type,
   last_updated_by emp_log_update.last_updated_by%type,
   last_update_date emp_log_update.last_update_date%type);
 
 TYPE emp_list IS TABLE OF emp_rec;
 emp_updates emp_list := emp_list();

BEFORE EACH ROW IS
 -- declare local timing point variables
 c number;
 -- stores the client info (identification, set up in the application)
 user_id varchar2(20) := nvl((sys_context('userenv','client_info')), -1);
BEGIN
 emp_updates.EXTEND;
 c := emp_updates.LAST;
 emp_updates(c).emp_log_id := emp_log_update_seq.nextval; 
 emp_updates(c).emp_id := :old.employee_id; 
 emp_updates(c).oldval := to_char(:old.salary); 
 emp_updates(c).newval := to_char(:new.salary); 
 emp_updates(c).created_by := user_id; 
 emp_updates(c).creation_date := sysdate; 
 emp_updates(c).last_updated_by := user_id; 
 emp_updates(c).last_update_date := sysdate; 
END BEFORE EACH ROW;
AFTER STATEMENT IS
BEGIN
 FORALL i in emp_updates.FIRST..emp_updates.LAST
   insert into emp_log_update
   values (
      emp_updates(i).emp_log_id, 
      emp_updates(i).emp_id, 
      emp_updates(i).oldval, 
      emp_updates(i).newval, 
      emp_updates(i).created_by,
      emp_updates(i).creation_date,
      emp_updates(i).last_updated_by,
      emp_updates(i).last_update_date
      );
END AFTER STATEMENT;
END;

Trigger created.

-- Set the client info: 
SQL> EXEC dbms_application_info.set_client_info('dev2');

PL/SQL procedure successfully completed.

SQL>update employees set salary=salary*1.15 where department_id=60;
5 rows updated.

SQL> select * from emp_event_log;
(formatted result..)
ID  EMP_ID  OLDVAL    NEWVAL   CREATED_BY   CREATION  LAST_UPDATED  LAST_UPDATED
                                              DATE         BY          DATE
--- ------- --------- -------- ------------ --------- ------------- ------------
  2     103 9000      10350    dev2         26-AUG-11 dev2          26-AUG-11
  3     104 6000      6900     dev2         26-AUG-11 dev2          26-AUG-11
  4     105 4800      5520     dev2         26-AUG-11 dev2          26-AUG-11
  5     106 4800      5520     dev2         26-AUG-11 dev2          26-AUG-11
  6     107 4200      4830     dev2         26-AUG-11 dev2          26-AUG-11

DBMS_APPLICATION_INFO package

Use DBMS_APPLICATION_INFO.{SET_CLIENT_INFO | READ_CLIENT_INFO} to maintain client info Read/Write column CLIENT_INFO on V$SESSION and V$SQL_AREA You can set module name or (trans)action name. Also useful with Oracle Trace and SQL Trace: to record names of modules or transactions for performance analysis or debugging. set action name before the start of a transaction. DBMSAPIN.SQL run by catproc #set module and transaction name CREATE or replace PROCEDURE add_employee( name VARCHAR2, salary NUMBER, manager NUMBER, title VARCHAR2, commission NUMBER, department NUMBER) AS BEGIN DBMS_APPLICATION_INFO.SET_MODULE( module_name => 'add_employee', action_name => 'insert into emp'); INSERT INTO emp (ename, empno, sal, mgr, job, hiredate, comm, deptno) VALUES (name, emp_seq.nextval, salary, manager, title, SYSDATE, commission, department); DBMS_APPLICATION_INFO.SET_MODULE(null,null); END; # Set transaction name CREATE OR REPLACE PROCEDURE bal_tran (amt IN NUMBER(7,2)) AS BEGIN -- balance transfer transaction DBMS_APPLICATION_INFO.SET_ACTION( action_name => 'transfer from chk to sav'); UPDATE chk SET bal = bal + :amt WHERE acct# = :acct; UPDATE sav SET bal = bal - :amt WHERE acct# = :acct; COMMIT; DBMS_APPLICATION_INFO.SET_ACTION(null); END; Other example: Session 1 (anonymous block): (a) set action and client info. (b) delete rows from employees Session 2: (a) query V$SESSION #SID 38: SQL> BEGIN dbms_application_info.set_action(action_name => 'delete from temp_emp'); dbms_application_info.set_client_info(client_info => 'Issued from terminal 2'); delete from temp_emp where city='Denver'; END; SQL> / PL/SQL procedure successfully completed #SID 2: SQL> select sid, serial#, username, osuser, module, action, client_info from v$session; (formatted output) SID SERIAL# USERNAME OSUSER MODULE ACTION CLIENT_INFO ---- -------- --------- -------- ---------- --------------------- ---------------------- 2 10 SYSTEM jdba SQL*Plus (...) 38 7 JFARR jdev SQL*Plus delete from temp_emp Issued from terminal 2 (...)