Triggers

Triggers are PL/SQL code that gets automatically fired on a specified event, they can call functions or procedures. You can implement triggers in either PL/SQL or Java, however you cannot call a trigger directly. There are five types of triggers

DDL triggers These triggers fire when you create, change or remove objects in a database schema.
DML triggers These triggers fire when you insert, update or delete data from a table. You can fire them once all changes to a table (statement trigger) or you can fire them for each row change (row-level trigger). You can use this type of trigger to audit, check, save or replace values before they are changed.
Compound triggers

These triggers acts as both statement and row-level triggers when you insert, update or delete data from a table. This trigger lets you capture information at four timing points

  • before firing the statement
  • before each row change from the firing statement
  • after each row change from the firing statement
  • after the firing statement

You can use this type of trigger to audit, check, save or replace values before they are changed.

Instead-of triggers These triggers enable you to stop performing a DML statement and redirect the DML statement, they are used normally when you write to non-updatable views.
System or Database event triggers These triggers fire when a system activity occurs in the database such as logon and logoff event triggers. This is useful you you want to audit who is accessing the system

Triggers can be used to check data integrity, perform audit and security related tasks. Triggers can be fired during the following

Trigger bodys can be no longer than 32,760 bytes, if you need to exceed this limit then either use a procedure or a function and call it from the trigger, you also cannot use a commit, rollback or savepoint statement in a trigger but this can be over come by using an autonomous transaction within the trigger. Triggers are dependant of the objects that they use which means a trigger can be invalid.

DDL Triggers

DDL triggers fire when you create, change or remove objects in a database, they support both before and after event triggers and work at the database or schema level.

DDL events alter, analyze, associate statistics, audit, comment, create, ddl, disassociate statistics, drop, grant, noaudit, rename, revoke, truncate

There are a number of event attribute functions that can be used to get user, client or system information

ORA_CLIENT_IP_ADDRESS returns the client IP address as varchar2
ORA_DATABASE_NAME returns database name as varchar2
ORA_DES_ENCRYPTED_PASSWORD returns DES-encrypted password as varchar2
ORA_DICT_OBJ_NAME returns object name as varchar2
ORA_DICT_OBJ_NAME_LIST returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_OWNER returns the owner of the object acted upon by the event as a varchar2
ORA_DICT_OBJ_OWNER_LIST returns the number of elements in the list as a pls_integer
ORA_DICT_OBJ_TYPE returns the datatype of the dictionary object changed by the event as a varchar2
ORA_GRANTEE returns the number of elements in the list as a pls_integer
ORA_INSTANCE_NUM returns the current database instance number as a number
ORA_IS_ALTER_COLUMN returns true or false depending if the column has been altered (true = altered)
ORA_IS_CREATING_NESTED_TABLE returns a true or false value when you create a table with a nested table
ORA_IS_DROP_COLUMN returns true or false depending if the column has been dropped (true = dropped)
ORA_IS_SERVERERROR returns true or false when the error is on the error stack
ORA_LOGIN_USER returns the current schema name as a varchar2
ORA_PARTITION_POS returns the numeric position with the SQL text where you can insert a partition clause
ORA_PRIVILEGE_LIST returns the number of elements in the list as a pls_integer
ORA_REVOKEE returns the number of elements in the list as a pls_integer
ORA_SERVER_ERROR returns the error number as a number
ORA_SERVER_ERROR_DEPTH returns the number of errors on the error stack as a pls_interger
ORA_SERVER_ERROR_MSG returns an error message text as a varchar2
ORA_SERVER_ERROR_NUM_PARAMS returns the count of any substituted strings from error messages as a pls_integer
ORA_SERVER_ERROR_PARAM returns an error message text as a varchar2
ORA_SQL_TXT returns the number of elements in the list as a pls_integer
ORA_SYSEVENT returns the system event that was responible for firing the trigger as a varchar2
ORA_WITH_GRANT_OPTION returns true or false when privileges are granted with grant option (true = with grant option)
SPACE_ERROR_INFO returns true or false when the triggering event is related to an out-of-space condition.

Now for an example

Prototype 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>;
Example

create or replace trigger audit_schema_trig
   before create on schema
begin
   insert into audit_schema_table values
     ( audit_schema_seq.nextval,
       ORA_DICT_OBJ_OWNER,                  -- notice the event attribute function
       ORA_DICT_OBJ_NAME,
       SYSDATE
     );
end audit_schema_trig;

Note: you need to create the table but you get the idea.

DML Triggers

DML triggers can fire before or after insert, update or delete statements, they also can be fired at statement or row-level. You cannot use use DCL in a DML trigger such as rollback, commit or savepoint however this can be overcome by using autonomous transaction.

Prototype create or replace trigger <trigger_name>
   {before | after }
   {insert | update | update of column1 | delete}
   on <table_name>
   [ for each row ]
   [ when (logical_expression) ]
[declare]
   [pragma autonomous_transaction;]
   declaration_statements
begin
   execution_statements
end <trigger_name>;
Statement Level Trigger create or replace trigger price1_trig
   after update of price_type on price
declare
   price_id number;
begin
   select price_log_seq.nextval into price_id from dual;
   insert into price_type_log values (price_log_seq.nextval, USER, SYSDATE);
end price1_trig;
/
Row Level Trigger

create or replace trigger contact_insert_trig
   before insert on contact
   for each row
   when (regexp_like(new.last_name,' '))
begin
   :new.last_name := regexp_replace(:new.last_name,' ','-',1,1);
end contact_insert_trig;
/

Note: you access the pseudo-field values using old or :old depending where you are in the trigger

old  - used in the when clause
:old - used when inside the trigger body

Compound Triggers

Compound triggers acts as both statement and row-level triggers when you insert, update or delete data from a table. Before this command it was quite complex to mimic this statement. The statement contains timing point selections which each can have there own declaration seelction, they are blocks of code executed during the specific timing point (before statement, after statement, before each row, after each row).

Prototype

create or replace trigger <trigger_name>
   for {insert | update | update column1 | delete}
   on <table>
compound trigger
   [before statement is                        -- before statement timing point
      [declaration_statement;]
   begin
      execution_statement;
   end before statement;]

   [before each row is                         -- before each row timing point
      [declaration_statement;]
   begin
      execution_statement;
   end before each row;]

   [after each row is                          -- after each row timing point
      [declaration_statement;]
   begin
      execution_statement;
   end after each row;]

   [after statement is                         -- after statement timing point
      [declaration_statement;]
   begin
      execution_statement;
   end after statement;]
end <trigger_name>;
/

Example create or replace trigger compound_connection_trig
   for insert on connection_log
   compound trigger
   before each row is
   begin
      if :new.event_id is NULL
      then
         :new.event_id := connection_log_seq.nextval;
      end if;
   end before each row;
end;
/  
Complex Example
-- Create table.
CREATE TABLE price_event_log
( price_log_id     NUMBER
, price_id         NUMBER
, created_by       NUMBER
, creation_date    DATE
, last_updated_by  NUMBER
, last_update_date DATE );
-- Create sequence. CREATE SEQUENCE price_event_log_s1; -- Set V$SESSION CLIENT_INFO value consistent with values in SYSTEM_USER table. EXEC dbms_application_info.set_client_info('3'); -- Create compound trigger on the price table. CREATE OR REPLACE TRIGGER compound_price_update_t1 FOR UPDATE ON price COMPOUND TRIGGER -- Declare a global record type. TYPE price_record IS RECORD ( price_log_id price_event_log.price_log_id%TYPE , price_id price_event_log.price_id%TYPE , created_by price_event_log.created_by%TYPE , creation_date price_event_log.creation_date%TYPE , last_updated_by price_event_log.last_updated_by%TYPE , last_update_date price_event_log.last_update_date%TYPE );
-- Declare a global collection type. TYPE price_list IS TABLE OF PRICE_RECORD;
-- Declare a global collection and initialize it. price_updates PRICE_LIST := price_list(); BEFORE EACH ROW IS -- Declare or define local timing point variables. c NUMBER; user_id NUMBER := NVL(TO_NUMBER(SYS_CONTEXT('userenv','client_info')),-1); BEGIN -- Extend space and assign dynamic index value. price_updates.EXTEND; c := price_updates.LAST; price_updates(c).price_log_id := price_event_log_s1.nextval; price_updates(c).price_id := :old.price_id; price_updates(c).created_by := user_id; price_updates(c).creation_date := SYSDATE; price_updates(c).last_updated_by := user_id; price_updates(c).last_update_date := SYSDATE; END BEFORE EACH ROW; AFTER STATEMENT IS BEGIN FORALL i IN price_updates.FIRST..price_updates.LAST INSERT INTO price_event_log VALUES ( price_updates(i).price_log_id , price_updates(i).price_id , price_updates(i).created_by , price_updates(i).creation_date , price_updates(i).last_updated_by , price_updates(i).last_update_date ); END AFTER STATEMENT; END; / -- Update PRICE table. UPDATE price SET last_updated_by = NVL(TO_NUMBER(SYS_CONTEXT('userenv','client_info')),-1); -- Query PRICE_EVENT_LOG table. SELECT * FROM price_event_log;

Instead-of Triggers

You can use instead-of triggers to intercept insert, update and delete statements and replace those instructions with alternative procedural code. Non-updatable views generally have instead-of triggers to accept the output and resolve the issues that make the view non-updatable.

Prototype create or replace trigger <trigger_name>
   instead of {dml_statement}
   on {object_name | database | schema}
   for each row
   [ when (logical expression)]
[declare]
   declaration_statements;
BEGIN
   execution_statements;
END <trigger_name>;
/
Example
-- Create the view
create or replace view account_list as
   select c.member_id, c.contact_id, m.account_number,
      c.first_name || decode(c.middle_initial,null,' ',' '||c.middle_initial||' ') || c.last_name FULL_NAME
   from contact c join member m on c.member_id = m.member_id;

-- Create trigger
CREATE OR REPLACE TRIGGER account_list_dml INSTEAD OF INSERT OR UPDATE OR DELETE ON account_list FOR EACH ROW DECLARE -- Source variable. source VARCHAR2(10);
--source account_list.full_name%TYPE := :new.full_name;
-- Parsed variables. fname VARCHAR2(43); mname VARCHAR2(1); lname VARCHAR2(43);
-- Check whether all dependents are gone. FUNCTION get_dependents (member_id NUMBER) RETURN BOOLEAN IS rows NUMBER := 0; CURSOR c (member_id_in NUMBER) IS SELECT COUNT(*) FROM contact WHERE member_id = member_id_in; BEGIN OPEN c (member_id); FETCH c INTO rows; IF rows > 0 THEN RETURN FALSE; ELSE RETURN TRUE; END IF; END get_dependents; BEGIN IF INSERTING THEN -- On insert event. RAISE_APPLICATION_ERROR(-20000,'Not enough data for insert!'); ELSIF UPDATING THEN -- On update event. -- Assign source variable. source := :new.full_name; -- Parse full_name for elements. fname := LTRIM(REGEXP_SUBSTR(source,'(^|^ +)([[:alpha:]]+)',1)); mname := REGEXP_SUBSTR( REGEXP_SUBSTR( source,'( +)([[:alpha:]]+)(( +|. +))',1),'([[:alpha:]])',1); lname := REGEXP_SUBSTR( REGEXP_SUBSTR( source,'( +)([[:alpha:]]+)( +$|$)',1),'([[:alpha:]]+)',1); -- Update name change in base table. UPDATE contact SET first_name = fname , middle_initial = mname , last_name = lname WHERE contact_id = :old.contact_id; ELSIF DELETING THEN -- On delete event. DELETE FROM contact WHERE member_id = :old.member_id; -- Only delete the parent when there aren't any more children. IF get_dependents(:old.member_id) THEN DELETE FROM member WHERE member_id = :old.member_id; END IF; END IF; END; /

System or Database Event Triggers

System triggers enable you to audit server startup, shutdown, server errors and user logon and logoff activities.

Prototype create or replace trigger <trigger_name>
   [before | after] database_event on {database | schema}
[declare]
   declaration_statements;
BEGIN
   execution_statements;
END <trigger_name>;
/
Example
-- Create connection audit log table.
CREATE TABLE connection_log 
( event_id           NUMBER(10),
  event_user_name VARCHAR2(30) CONSTRAINT log_event_nn1 NOT NULL,   event_type VARCHAR2(14) CONSTRAINT log_event_nn2 NOT NULL,   event_date DATE CONSTRAINT log_event_nn3 NOT NULL,   CONSTRAINT connection_log_p1 PRIMARY KEY (event_id)
); -- Create sequence starting at 1 and incrementing by 1. CREATE SEQUENCE connection_log_s1; -- Create a trigger to automate the primary (surrogate) key generation. CREATE OR REPLACE TRIGGER connection_log_t1 BEFORE INSERT ON connection_log FOR EACH ROW WHEN (new.event_id IS NULL) BEGIN SELECT connection_log_s1.nextval INTO :new.event_id FROM dual; END; / -- Grant access rights to PHP user. GRANT SELECT ON connection_log TO PHP; -- Define a package with connecting and disconnecting procedures. CREATE OR REPLACE PACKAGE user_connection AS PROCEDURE Connecting (user_name IN VARCHAR2);   PROCEDURE Disconnecting (user_name IN VARCHAR2); END user_connection; / -- Define a package body with procedure implementation details. CREATE OR REPLACE PACKAGE BODY user_connection AS PROCEDURE connecting (user_name IN VARCHAR2) IS BEGIN INSERT INTO connection_log (event_user_name, event_type, event_date) VALUES (user_name,'CONNECT',SYSDATE); END connecting; PROCEDURE disconnecting (user_name IN VARCHAR2) IS BEGIN INSERT INTO connection_log (event_user_name, event_type, event_date) VALUES (user_name,'DISCONNECT',SYSDATE); END disconnecting; END user_connection; / -- Define system login trigger. CREATE OR REPLACE TRIGGER connecting_trigger AFTER LOGON ON DATABASE BEGIN user_connection.connecting(sys.login_user); END; / -- Define system logout trigger. CREATE OR REPLACE TRIGGER disconnecting_trigger BEFORE LOGOFF ON DATABASE BEGIN user_connection.disconnecting(sys.login_user); END; /

Trigger Restrictions

There are a number of restrictions that triggers have

Trigger Maintenance

Enabling alter trigger test_trigger enable;
alter table test enable all triggers;
Disabling alter trigger test_trigger disable;
alter table test disable all triggers;
Recompiling alter trigger test_trigger compile;
Detect broken code select object_name from dba_objects where status = 'INVALID';
Useful Views
DBA_TRIGGERS describes all triggers in the database
DBA_PROCEDURES lists all functions and procedures along with their associated properties
DBA_SOURCE describes the text source of all stored objects in the database
DBA_CATALOG lists all indexes, tables, views, clusters, synonyms, and sequences in the database

Using Triggers for Exception Management

Triggers can solve two types of problems: how to handle critical error and non-critical errors, you can raise and record exceptions but allow processing for non-critical errors.

Critical Error

create or replace trigger critical_err
before insert on contact
for each row
DECLARE
   pramga autonomous_transaction    -- This allows us to commit inside the trigger
   cursor c (member_id_in number) IS
      select null from contact c, member m
      where c.member_id = m.member_id AND c.member_id = member_id_in
      having count(*) > 1:
BEGIN
   for i in c (:new.member_id)
   loop
      record( send information );   -- this is you own function to collect the information you want
   end loop;
   commit;                          -- because we use autonomous transaction we can commit the log entry
   raise_application_error(-20001, 'Already two signers.');    -- Raise an exception and stop the original trigger action

END;
/

Note: we use the autonomous transaction to allow us to commit inside the trigger, because we voilate the trigger we do not allow this transaction because of the raise_application_error

non-critical Error

create or replace trigger critical_err
before insert on contact
for each row
DECLARE
   pramga autonomous_transaction    -- This allows us to commit inside the trigger
   cursor c (member_id_in number) IS
      select null from contact c, member m
      where c.member_id = m.member_id AND c.member_id = member_id_in
      having count(*) > 1:
BEGIN
   for i in c (:new.member_id)
   loop
      record( send information );   -- this is you own function to collect the information you want
   end loop;
   -- commit;                          -- because we use autonomous transaction we can commit the log entry
   -- raise_application_error(-20001, 'Already two signers.');    -- Raise an exception and stop the original trigger action

END;
/

Note: because we not to fussed that we voilate the rule we allow it to proceed, we could of course allow the commit to the log file.

Redo Log and Triggers

Different triggers use different amounts of redo information, apart from the delete both insert and update will produce redo log information.

DML Operation AFTER trigger BEFORE trigger
DELETE No affect No affect
INSERT Same amount a 9i Same amount as 9i
UPDATE Increase redo compare with 9i Increased redo compared with 9i