Practice with Triggers - Part 1

An Introduction to Triggers

Oracle allows us to define procedures that are implicitly executed when an INSERT, UPDATE, or DELETE statement is issued against the associated table. These procedures are called database triggers.

Triggers are similar to stored procedures. a trigger can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. While a procedure is explicitly executed by a user, application, or trigger, one or more triggers are implicitly fired (executed) by Oracle when a triggering INSERT, UPDATE, or DELETE statement is issued, no matter which user is connected or which application is being used.

Triggers are written to be executed in response to any of the following events

·        A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)

If the trigger is created on a table or view, then the triggering event is composed of DML statements, and the trigger is called a DML trigger.

System trigger

If the trigger is created on a schema or the database, then the triggering event is composed of either DDL or database operation statements, and the trigger is called a system trigger. 

·        A database definition (DDL) statement (CREATE, ALTER, or DROP).

·        A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN).

Triggers can be defined on the table, view, schema, or database with which the event is associated.

A conditional trigger has a WHEN clause that specifies a SQL condition that the database evaluates for each row that the triggering statement affects.

When a trigger fires, tables that the trigger references might be undergoing changes made by SQL statements in other user's transactions. SQL statements running in triggers follow the same rules that standalone SQL statements do. Specifically:

·         Queries in the trigger see the current read-consistent materialized view of referenced tables and any data changed in the same transaction.

·         Updates in the trigger wait for existing data locks to be released before proceeding.

An INSTEAD OF trigger is either:

·         A DML trigger created on either a noneditioning view or a nested table column of a noneditioning view

·         A system trigger defined on a CREATE statement

The database fires the INSTEAD OF trigger instead of running the triggering statement.

Note: A trigger is often called by the name of its triggering statement (for example, DELETE trigger or LOGON trigger), the name of the item on which it is defined (for example, DATABASE trigger or SCHEMA trigger), or its timing point (for example, BEFORE statement trigger or AFTER each row trigger).

How Triggers Are Used

In many cases, triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can permit DML operations against a table only if they are issued during regular business hours. The standard security features of Oracle, roles and privileges, govern which users can submit DML statements against the table. In addition, the trigger further restricts DML operations to occur only at certain times during weekdays. This is just one way that we can use triggers to customize information management in an Oracle database.

In addition, triggers are commonly used to

• automatically generate derived column values

• prevent invalid transactions

• enforce complex security authorizations

• Enforce referential integrity when child and parent tables are on different nodes of a distributed database

• enforce complex business rules

• provide transparent event logging

• provide sophisticated auditing

• maintain synchronous table replicates

• gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing applications
• Prevent DML operations on a table after regular business hours
• Enforce complex business or referential integrity rules that we cannot define with constraints

A Cautionary Note about Trigger Use

When a trigger is fired, a SQL statement within its trigger action potentially can fire other triggers. When a statement in a trigger body causes another trigger to be fired, the triggers are said to be cascading.

While triggers are useful for customizing a database, we should only use triggers when necessary. The excessive use of triggers can result in complex interdependences, which may be difficult to maintain in a large application.

CYCLIC CASCADING in a TRIGGER

This is an undesirable situation where more than one trigger enter into an infinite loop. while creating a trigger we should ensure the such a situation does not exist.

The below example shows how Trigger's can enter into cyclic cascading.

Let's consider we have two tables 'abc' and 'xyz'. Two triggers are created.
1) The INSERT Trigger, triggerA on table 'abc' issues an UPDATE on table 'xyz'.
2) The UPDATE Trigger, triggerB on table 'xyz' issues an INSERT on table 'abc'.

In such a situation, when there is a row inserted in table 'abc', triggerA fires and will update table 'xyz'.
When the table 'xyz' is updated, triggerB fires and will insert a row in table 'abc'.
This cyclic situation continues and will enter into a infinite loop, which will crash the database.

Creating Triggers

The syntax for creating a trigger is −

CREATE [OR REPLACE] TRIGGER trigger_name  

{BEFORE | AFTER | INSTEAD OF}  

{INSERT [OR] | UPDATE [OR] | DELETE}  

[OF col_name]  

ON table_name  

[REFERENCING OLD AS o NEW AS n]  

[FOR EACH ROW | FOR EACH STATEMENT]  

WHEN (condition)   

DECLARE 

   Declaration-statements 

BEGIN  

   Executable-statements 

EXCEPTION 

   Exception-handling-statements 

END; 

Where,

·  CREATE [OR REPLACE] TRIGGER trigger_name − Creates or replaces an existing trigger with the trigger_name.

·       {BEFORE | AFTER | INSTEAD OF} − This specifies when the trigger will be executed. The INSTEAD OF clause is used for creating trigger on a view.

·        {INSERT [OR] | UPDATE [OR] | DELETE} − This specifies the DML operation.

·        [OF col_name] − This specifies the column name that will be updated.

·        [ON table_name] − This specifies the name of the table associated with the trigger.

·      [REFERENCING OLD AS o NEW AS n] − This allows you to refer new and old values for various DML statements, such as INSERT, UPDATE, and DELETE.

·       [FOR EACH ROW | FOR EACH STATEMENT] − This specifies a row-level trigger, i.e., the trigger will be executed for each row being affected. Otherwise the trigger will execute just once when the SQL statement is executed, which is called a statement/table level trigger.

·        WHEN (condition) − This provides a condition for rows for which the trigger would fire. This clause is valid only for row-level triggers.

How To know Information about Triggers

We can use the data dictionary view 'USER_TRIGGERS' to obtain information about any trigger.

The below statement shows the structure of the view 'USER_TRIGGERS'

 DESC USER_TRIGGERS;

This view stores information about header and body of the trigger.

SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';

You can drop a trigger using the following command.

DROP TRIGGER trigger_name;

System Triggers

A system trigger is created on either a schema or the database. Its triggering event is composed of either DDL statements or database operation statements.

 

A system trigger fires at exactly one of these timing points:

• Before the triggering statement runs

(The trigger is called a BEFORE statement trigger or statement-level BEFORE trigger.)

• After the triggering statement runs

(The trigger is called a AFTER statement trigger or statement-level AFTER trigger.)

• Instead of the triggering CREATE statement

(The trigger is called an INSTEAD OF CREATE trigger.)

 

Oracle DDL Triggers 
One or more types of DDL SQL statements that can cause the trigger to fire. We can create triggers for these events on DATABASE or SCHEMA unless otherwise noted. We can create BEFORE and AFTER triggers for any of these events, but We can create INSTEAD OF triggers only for CREATE events. The database fires the trigger in the existing user transaction.

Note: Some objects are created, altered, and dropped using PL/SQL APIs (for example, scheduler jobs are maintained by subprograms in the DBMS_SCHEDULER package). Such PL/SQL subprograms do not fire DDL triggers.

The following ddl_event values are valid:

• ALTER

Causes the database to fire the trigger whenever an ALTER statement modifies a database object in the data dictionary. An ALTER DATABASE statement does not fire the trigger.

• ANALYZE

Causes the database to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.

• ASSOCIATE STATISTICS

Causes the database to fire the trigger whenever the database associates a statistics type with a database object.

• AUDIT

Causes the database to fire the trigger whenever an AUDIT statement is issued.

• COMMENT

Causes the database to fire the trigger whenever a comment on a database object is added to the data dictionary.

• CREATE

Causes the database to fire the trigger whenever a CREATE statement adds a database object to the data dictionary. The CREATE DATABASE or CREATE CONTROLFILE statement does not fire the trigger.

• DISASSOCIATE STATISTICS

Causes the database to fire the trigger whenever the database disassociates a statistics type from a database object.

• DROP

Causes the database to fire the trigger whenever a DROP statement removes a database object from the data dictionary.

• GRANT

Causes the database to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.

• NOAUDIT

Causes the database to fire the trigger whenever a NOAUDIT statement is issued.

• RENAME

Causes the database to fire the trigger whenever a RENAME statement changes the name of a database object.

• REVOKE

Causes the database to fire the trigger whenever a REVOKE statement removes system privileges or roles or object privileges from a user or role.

• TRUNCATE

Causes the database to fire the trigger whenever a TRUNCATE statement removes the rows from a table or cluster and resets its storage characteristics.

• DDL

Causes the database to fire the trigger whenever any of the preceding DDL statements is issued.

Using Oracle DDL Triggers

Auditing database changes: Oracle provides DDL triggers to audit all schema changes and can report the exact change, when it was made, and by which user.  There are several ways to audit within Oracle and the following auditing tools are provided:

• SQL audit command (for DML)

• Auditing with object triggers (DML auditing)

• Auditing with system-level triggers (DML and DDL) 

• Auditing with LogMiner (DML and DDL) 

• Fine-grained auditing (select auditing)

DDL triggers: Using the Data Definition Language (DDL) triggers, the Oracle DBA can automatically track all changes to the database, including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA.

DDL triggers execute every time a DDL statement is executed, and adds new entries to your new table, as shown below:

connect sys/manager

create or replace trigger
DDLTrigger
AFTER DDL ON DATABASE
BEGIN
insert into
perfstat.stats$ddl_log
(
user_name,
ddl_date,
ddl_type,
object_type,
owner,
object_name
)
VALUES
(
ora_login_user,
sysdate,
ora_sysevent,
ora_dict_obj_type,
ora_dict_obj_owner,
ora_dict_obj_name
);

END;
/

What is truly exciting is that we can combine the system-level triggers with STATSPACK extension tables, thereby building a mechanism that will give complete auditing information on user logon and logoff, DDL, and server errors.

DDL triggers -- Using Oracle DDL triggers, the Oracle administrator can automatically track all changes to the database including changes to tables, indexes, and constraints. The data from this trigger is especially useful for change control for the Oracle DBA. 

DDL triggers script for DDL auditing:

CREATE TABLE AUDIT_DDL (
  d date,
  OSUSER varchar2(255),
  CURRENT_USER varchar2(255),
  HOST varchar2(255),
  TERMINAL varchar2(255),
  owner varchar2(30),
  type varchar2(30),
  name varchar2(30),
  sysevent varchar2(30));

create or replace trigger audit_ddl_trg after ddl on schema
begin
  if (ora_sysevent='TRUNCATE')
  then
    null; -- I do not care about truncate
  else
    insert into audit_ddl(d, osuser,current_user,host,terminal,owner,type,name,sysevent)
    values(
      sysdate,
      sys_context('USERENV','OS_USER') ,
      sys_context('USERENV','CURRENT_USER') ,
      sys_context('USERENV','HOST') ,
      sys_context('USERENV','TERMINAL') ,
      ora_dict_obj_owner,
      ora_dict_obj_type,
      ora_dict_obj_name,
      ora_sysevent
    );
  end if;
end;
/

Now whenever a change is made, the details will be written to the table, and we can create audit reports for management. This report is critical for the Oracle DBA who must track changes to his or her production database. This report uses the DDL trigger and produces a complete audit log of all Oracle databases changes, as shown below:

DDL triggers execute every time a DDL statement is executed and adds new entries to the stats$ddl_log table.

DDL table -- Using the above code snippet, we create an Oracle table to capture all of the salient metrics required to do effective change management within an Oracle environment. Note this table contains the date that the DDL was made, the ID of the user who originated the DDL change, the type of the object, and the object's name. This information can be quite useful for tracking purposes.

DDL Reports - This report is critical for the Oracle DBA who must track changes to their production database. This report uses Oracle DDL triggers and produces a complete audit log of all Oracle databases changes.

Database operation triggers (database_event)

Just as an Oracle trigger fires on a specific DML event, system-level triggers are fired at specific system events such as logon, logoff, database startup, DDL execution, and servererror triggers.

We can create triggers for these events on either DATABASE or SCHEMA.

Each database event is valid in either a BEFORE trigger or an AFTER trigger, but not both. For each of these triggering events, the database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).

• AFTER STARTUP

Causes the database to fire the trigger whenever the database is opened. This event is valid only with DATABASE, not with SCHEMA.

• BEFORE SHUTDOWN

Causes the database to fire the trigger whenever an instance of the database is shut down. This event is valid only with DATABASE, not with SCHEMA.

• AFTER DB_ROLE_CHANGE

In a Data Guard configuration, causes the database to fire the trigger whenever a role change occurs from standby to primary or from primary to standby. This event is valid only with DATABASE, not with SCHEMA.

• AFTER SERVERERROR

Causes the database to fire the trigger whenever both conditions are true:

o    A server error message is logged.

o    Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers.

Examples of when it is unsafe to fire error triggers include:

§  RDBMS is starting up.

§  A critical error has occurred.

• AFTER LOGON

Causes the database to fire the trigger whenever a client application logs onto the database.

• BEFORE LOGOFF

Causes the database to fire the trigger whenever a client application logs off the database.

• AFTER SUSPEND

Causes the database to fire the trigger whenever a server error causes a transaction to be suspended.

SCHEMA Triggers

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.

Suppose that both user1 and user2 own schema triggers, and user1 invokes a DR unit owned by user2. Inside the DR unit, user2 is the current user. Therefore, if the DR unit initiates the triggering event of a schema trigger that user2 owns, then that trigger fires. However, if the DR unit initiates the triggering event of a schema trigger that user1 owns, then that trigger does not fire.

Example creates a BEFORE statement trigger on the sample schema HR. When a user connected as HR tries to drop a database object, the database fires the trigger before dropping the object.

Example: BEFORE Statement Trigger on Sample Schema HR

CREATE OR REPLACE TRIGGER drop_trigger

  BEFORE DROP ON hr.SCHEMA

  BEGIN

    RAISE_APPLICATION_ERROR (

      num => -20000,

      msg => 'Cannot drop object');

  END;

/

 

DATABASE Triggers

A DATABASE trigger is created on the database and fires whenever any database user initiates the triggering event.

Example shows the basic syntax for a trigger to log errors. This trigger fires after an unsuccessful statement execution, such as unsuccessful logon.

Note:

An AFTER SERVERERROR trigger fires only if Oracle relational database management system (RDBMS) determines that it is safe to fire error triggers. For more information about AFTERSERVERERROR triggers, see "AFTER SERVERERROR".

Example:  AFTER Statement Trigger on Database

CREATE TRIGGER log_errors

  AFTER SERVERERROR ON DATABASE

  BEGIN

    IF (IS_SERVERERROR (1017)) THEN

      NULL;  -- (substitute code that processes logon error)

    ELSE

      NULL;  -- (substitute code that logs error code)

    END IF;

  END;

/

The trigger in Example runs the procedure check_user after a user logs onto the database.

Example: Trigger Monitors Logons

CREATE OR REPLACE TRIGGER check_user

  AFTER LOGON ON DATABASE

  BEGIN

    check_user;

  EXCEPTION

    WHEN OTHERS THEN

      RAISE_APPLICATION_ERROR

        (-20000, 'Unexpected error: '|| DBMS_Utility.Format_Error_Stack);

 END;

/

 

INSTEAD OF CREATE Triggers

An INSTEAD OF CREATE trigger is a SCHEMA trigger whose triggering event is a CREATE statement. The database fires the trigger instead of executing its triggering statement.

Example shows the basic syntax for an INSTEAD OF CREATE trigger on the current schema. This trigger fires when the owner of the current schema issues a CREATE statement in the current schema.

Example: INSTEAD OF CREATE Trigger on Schema

CREATE OR REPLACE TRIGGER t

  INSTEAD OF CREATE ON SCHEMA

  BEGIN

    EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';

  END;

/

I hope you all have enjoyed reading this article. Comments are welcome....


Related Posts:
-DML Triggers - Part 2
-AUTONOMOUS_TRANSACTION
-TRIGGER - Examples