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
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 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 Note: you access the pseudo-field values using old or :old depending where you are in the trigger |
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> [before each row is -- before each row timing point [after each row is -- after each row timing point [after statement is -- after statement timing point |
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 ); |
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 |
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), |
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 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 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 |