Sql : Trigger

A trigger is a special kind of stored procedure that automatically executes when an event occurs in the database server.

Read a brief introduction about Triggers in Oracle : Database Objects Introduction #Database Triggers post. Assuming you have already gone through the link and got the basic understaning about the triggers, now lets go further.

Index

1. Introduction to SQL Triggers
2. Parts of a Trigger
1. The Triggering Event or Statement
2. Trigger Restriction
3. Trigger Action
3. Types of Triggers
1. Row Triggers and Statement Triggers
2. BEFORE and AFTER Triggers
3. Compound Triggers
4. INSTEAD OF Triggers
5. Triggers on System Events and User Events
4. Order of Trigger Firing
5. Enable and Disable Triggers
6. Creating Triggers
1. Simple DML Trigger
2. Instead Of DML Trigger
3. Compound DML Trigger
4. System Trigger
7. Trigger Examples
1. Simple DML Triggers
• BEFORE statement trigger
• Trigger Uses Conditional Predicates to Detect Triggering Statement
• Trigger Logs Changes to EMPLOYEES.SALARY
• Conditional Trigger Prints Salary Change Information
• Trigger Modifies LOB Columns
• Trigger with REFERENCING Clause
• Trigger References OBJECT_VALUE Pseudocolumn
• Execution Order of Triggers using FOLLOWS Example
• Calling a Procedure in a Trigger Body Example
2. INSTEAD OF DML Triggers
• Creating an INSTEAD OF Trigger Example
3. Compound DML Triggers
• Compound Trigger Example
• Compound Trigger Logs Changes to One Table in Another Table
4. Triggers for Ensuring Referential Integrity
• UPDATE and DELETE SET NULL Trigger for Parent Table
• DELETE CASCADE Trigger for Parent Table
• Foreign Key Trigger for Child Table
• UPDATE and DELETE RESTRICT Trigger for Parent Table
5. System Triggers
• Creating a SCHEMA Trigger
• Creating a Database Event Trigger
• Creating a DDL Trigger Examples
• Trigger Monitors Logons
• INSTEAD OF CREATE Trigger on Schema
6. Miscellaneous Trigger Examples
• Trigger Invokes Java Subprogram

Introduction to SQL Triggers

Triggers are procedures stored in PL/SQL or Java that run (fire) implicitly (if it is in the enabled state) whenever a table or view is modified or when some user actions or database system actions occur.

If the trigger is in the enabled state, the triggering_event causes the database to execute the triggered_action if the trigger_restriction is either TRUE or omitted. The triggering_event is associated with either a table, a view, a schema, or the database, and it is one of these:

• DML statement (described in "About Data Manipulation Language (DML) Statements")
• DDL statement (described in "About Data Definition Language (DDL) Statements")
• Database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

If the trigger is in the disabled state, the triggering_event does not cause the database to execute the triggered_action, even if the trigger_restriction is TRUE or omitted.

By default, a trigger is created in the enabled state. You can disable an enabled trigger, and enable a disabled trigger.

Unlike a subprogram, a trigger cannot be invoked directly. A trigger is invoked only by its triggering event, which can be caused by any user or application. You might be unaware that a trigger is executing unless it causes an error that is not handled properly.

A trigger has the following structure:

TRIGGER trigger_name
  triggering_event/ triggering_statement
  [ trigger_restriction ]
BEGIN
  triggered_action;
END;

Parts of a Trigger

A trigger has three basic parts, the following figure represents each of these parts of a trigger and is not meant to show exact syntax. The sections that follow explain each part of a trigger in greater detail.

Fig 1.1 Sql Trigger - Parts

The Triggering Event or Statement

A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:
• An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)
• A CREATE, ALTER, or DROP statement on any schema object
• A database startup or instance shutdown
• A specific error message or any error message
• A user logon or logoff
For Example:

-- fire the trigger when table_A table is updated 
... UPDATE ON table_A ... 

-- fire the trigger when colA column of a row in the table_A table is updated 
... UPDATE OF colA ON table_A ... 
-- column list can be specified in UPDATE statements only

-- fire the trigger when an INSERT, UPDATE, or DELETE statement is issued against table_A
... INSERT OR UPDATE OR DELETE ON table_A ... 
-- a triggering event can specify multiple SQL statements

Trigger Restriction

A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown.
In the example, the trigger restriction is:

-- does not fire trigger unless the value of parts_on_hand is less than present reorder_point
new.parts_on_hand < new.reorder_point 

-- fire trigger when the present value of reorder_point is more than previous reorder_point
new.reorder_point > old.reorder_point

Trigger Action

A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when a triggering statement is issued and the trigger restriction evaluates to true. Like stored procedures, a trigger action can:
• Contain SQL, PL/SQL, or Java statements
• Define PL/SQL language constructs such as variables, constants, cursors, exceptions
• Define Java language constructs
• Call stored procedures
For Example:

   BEGIN
      DBMS_OUTPUT.PUT_LINE('TRIGGER FIRED!!');
   END;
------------------------------------------------
   BEGIN
      RAISE_APPLICATION_ERROR (
         num => -20000,
         msg => 'Cannot drop object');
   END;
------------------------------------------------
   DECLARE 
    A_NAME VARCHAR2(100);
   BEGIN
    A_NAME:='ANY_NAME';
    .. pl/sql_block ..
   END;

About OLD and NEW Pseudorecords

When a row-level trigger fires, the PL/SQL runtime system creates and populates the two pseudorecords OLD and NEW. They are called pseudorecords because they have some, but not all, of the properties of records.

For the row that the trigger is processing:
• For an INSERT trigger, OLD contains no values, and NEW contains the new values.
• For an UPDATE trigger, OLD contains the old values, and NEW contains the new values.
• For a DELETE trigger, OLD contains the old values, and NEW contains no values.
To reference a pseudorecord, put a colon before its name—:OLD or :NEW

... (new.reorder_point > old.reorder_point) ...

You can also have your own custom names by using REFERENCING:

... REFERENCING OLD AS OLDEST NEW AS NEWEST ...
..
... (NEWEST.reorder_point > OLDEST.reorder_point) ...
-- OLD AS OLDEST is applicable for update statements

Types of Triggers

The different types of triggers:

Row Triggers and Statement Triggers

When you define a trigger, you can specify the number of times the trigger action is to be run:
• Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
• Once for the triggering statement, no matter how many rows it affects

Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.

Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example:

CREATE TRIGGER salary_check
   BEFORE INSERT OR UPDATE OF salary, job_id ON employees
   FOR EACH ROW  -- Executes for each row
   WHEN (new.job_id <> 'AD_VP') -- 'new' variable holds the row's data 
   BEGIN
   DBMS_OUTPUT.PUT_LINE('Table Row trigger fired with' ||:new.job_id || :new.salary);
   END;
   /

Statement Triggers

A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.

Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
• Make a complex security check on the current time or user
• Generate a single audit record
For example:

CREATE TRIGGER salary_check
   BEFORE INSERT OR UPDATE OF salary, job_id ON employees
   BEGIN
-- Note that the below 'new' variable reference will not compile in Statement Triggers
-- DBMS_OUTPUT.PUT_LINE('Table Row trigger fired with' ||:new.job_id || :new.salary);
   DBMS_OUTPUT.PUT_LINE('Table Statement trigger fired.');
   END;
   /

BEFORE and AFTER Triggers

When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.

BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.



BEFORE Triggers

BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:
• When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
• To derive specific column values before completing a triggering INSERT or UPDATE statement.
For Example:

CREATE TRIGGER schema.trigger_name 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block




AFTER Triggers

AFTER triggers run the trigger action after the triggering statement is run. For Example:

CREATE TRIGGER schema.trigger_name 
    AFTER
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block




Trigger Type Combinations

Using the options listed previously, you can create four types of row and statement triggers:
• BEFORE statement trigger : Before executing the triggering statement, the trigger action is run.
• BEFORE row trigger : Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
• AFTER statement trigger : After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
• AFTER row trigger : After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.

You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE).

Compound Triggers

A compound trigger allows code for one or more timing points for a specific object to be combined into a single trigger. The individual timing points can share a single global declaration section, whose state is maintained for the lifetime of the statement. Once a statement ends, due to successful completion or an error, the trigger state is cleaned up.

The triggering actions are defined in the same way as any other DML trigger, with the addition of the COMPOUND TRIGGER clause. The main body of the trigger is made up of an optional global declaration section and one or more timing point sections, each of which may contain a local declaration section whose state is not maintained.

For Example:

CREATE OR REPLACE TRIGGER 
  FOR  ON 
    COMPOUND TRIGGER

  -- Global declaration.
  g_global_variable VARCHAR2(10);

  BEFORE STATEMENT IS
  BEGIN
    NULL; -- Do something here.
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    NULL; -- Do something here.
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    NULL; -- Do something here.
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    NULL; -- Do something here.
  END AFTER STATEMENT;

END ;
/

INSTEAD OF Triggers

An INSTEAD OF trigger is defined on a view, and its triggering event is a DML statement. Instead of executing the DML statement, Oracle Database executes the INSTEAD OF trigger.

INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.

You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified. For Example:

CREATE OR REPLACE TRIGGER update_name_view_trigger
INSTEAD OF UPDATE ON emp_locations
BEGIN
  UPDATE employees SET
    first_name = substr( :NEW.name, instr( :new.name, ',' )+2),
    last_name = substr( :NEW.name, 1, instr( :new.name, ',')-1)
  WHERE employee_id = :OLD.employee_id;
END;

Triggers on System Events and User Events

A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each event associated with the owner of the schema (the current user). A trigger defined on a database fires for each event associated with all users.

You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:
1. System events
• Database startup and shutdown
• Data Guard role transitions
• Server error message events
2. User events
• User logon and logoff
• DDL statements (CREATE, ALTER, and DROP)
• DML statements (INSERT, DELETE, and UPDATE)
For Example:

CREATE TRIGGER register_shutdown 
  ON DATABASE 
  SHUTDOWN 
    BEGIN 
    ...
    DBMS_AQ.ENQUEUE(...); 
    ... 
    END;

Triggers on DDL statements or logon/logoff events can also be defined at the database level or schema level. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.

Order of Trigger Firing

If two or more triggers with different timing points (BEFORE, AFTER, INSTEAD OF) are defined for the same statement on the same table, then they fire in the following order:

• All BEFORE statement triggers
• All BEFORE row triggers
• All AFTER row triggers
• All AFTER statement triggers

FOLLOWS / PRECEDES

This clause lets you specify the relative firing order of triggers of the same type. Use FOLLOWS to indicate that the trigger being created should fire after the specified triggers.

The specified triggers must already exist, they must be defined on the same table as the trigger being created, and they must have been successfully compiled. They need not be enabled.

You can specify FOLLOWS in the definition of a simple trigger with a compound trigger target, or in the definition of a compound trigger with a simple trigger target. In these cases, the FOLLOWS keyword applies only to the section of the compound trigger with the same timing point as the sample trigger. If the compound trigger has no such timing point, then FOLLOWS is quietly ignored.

For Example, we can specify that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by creating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWS clause:

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
--Now the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/

Enable and Disable Triggers

It has been possible to enable and disable triggers for some time using the ALTER TRIGGER and ALTER TABLE commands.

• ALTER TRIGGER DISABLE;
• ALTER TRIGGER ENABLE;
• ALTER TABLE DISABLE ALL TRIGGERS;
• ALTER TABLE ENABLE ALL TRIGGERS;

Prior to 11g, it was only possible to create triggers in the enabled state, then subsequently disable them. Now they can be explicitly enabled or disabled at creation time, with the enabled state as the default.

CREATE OR REPLACE TRIGGER trigger_control_test_trg
BEFORE INSERT ON trigger_control_test
FOR EACH ROW
ENABLE
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_CONTROL_TEST_TRG - Executed');
END;
/

Creating Triggers

The CREATE TRIGGER statement creates or replaces a database trigger, which is either of the following:

• A stored PL/SQL block associated with a table, a schema, or the database
• An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java

The database automatically executes a trigger when specified conditions occur.

What are the Prerequisites for creating a Trigger?

• To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the CREATE TRIGGER system privilege.
• To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the CREATE ANY TRIGGER system privilege.
• In addition to the preceding privileges, to create a trigger on DATABASE, you must have the ADMINISTER DATABASE TRIGGER system privilege.
• If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.

Syntax

• Simple DML Trigger

--simple_dml_trigger
CREATE [OR REPLACE] TRIGGER [schema.] trigger_name
--trigger_timing 
{ BEFORE | AFTER }
--dml_event_clause
{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
ON [ schema.] { table | view }
--referencing_clause
[REFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent }... ]
--row_trigger
[ FOR EACH ROW ]
--trigger_edition_clause
[ { FORWARD | REVERSE } CROSSEDITION ]
--trigger_ordering_clause
[ { FOLLOWS | PRECEDES } [ schmema.] trigger [ , [ schmema.] trigger ]... ]
--trigger_enable_disable
[ ENABLE | DISABLE ]
--trigger_restriction_clause
[ WHEN ( condition ) ]
--trigger_body
{ plsql_block | CALL routine_clause }

• Instead Of DML Trigger

CREATE [OR REPLACE] TRIGGER [schema.] trigger_name
--instead_of_dml_trigger
INSTEAD OF { DELETE | INSERT | UPDATE } [ OR { DELETE | INSERT | UPDATE } ]...
ON [ NESTED TABLE nested_table_column OF ] [ schema. ] noneditioning_view
--referencing_clause
[REFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent }... ]
--row_trigger
[ FOR EACH ROW ]
--trigger_edition_clause
[ { FORWARD | REVERSE } CROSSEDITION ]
--trigger_ordering_clause
[ { FOLLOWS | PRECEDES } [ schmema.] trigger [ , [ schmema.] trigger ]... ]
--trigger_enable_disable
[ ENABLE | DISABLE ]
--trigger_body
{ plsql_block | CALL routine_clause }

• Compound DML Trigger

CREATE [OR REPLACE] TRIGGER [schema.] trigger_name
--compound_dml_trigger
FOR 
--dml_event_clause
{ DELETE | INSERT | UPDATE [ OF column [, column ]... ] }
[ OR { DELETE | INSERT | UPDATE [ OF column [, column]... ] }...
ON [ schema.] { table | view }
--referencing_clause
[REFERENCING { OLD [ AS ] old | NEW [ AS ] new | PARENT [ AS ] parent }... ]
--trigger_edition_clause
[ { FORWARD | REVERSE } CROSSEDITION ]
--trigger_ordering_clause
[ { FOLLOWS | PRECEDES } [ schmema.] trigger [ , [ schmema.] trigger ]... ]
--trigger_enable_disable
[ ENABLE | DISABLE ] 
--trigger_restriction_clause
[ WHEN ( condition ) ]
--compound_trigger_body
COMPOUND TRIGGER
  --global_common_declaration
  [ declare_section ]
  --timing_point_section
    --timing_point
    {{ BEFORE STATEMENT | BEFORE EACH ROW | AFTER STATEMENT | AFTER EACH ROW |  INSTEAD OF EACH ROW }
     IS BEGIN 
     --tps_body 
     statement [ statement | pragma ]...
    [ EXCEPTION exception_handler [ exception_handler ]... ]
   END { BEFORE STATEMENT | BEFORE EACH ROW | AFTER STATEMENT | AFTER EACH ROW |  INSTEAD OF EACH ROW } ;}...
END [ trigger ] ;

• System Trigger

CREATE [OR REPLACE] TRIGGER [schema.] trigger_name
--system_trigger  
{ BEFORE | AFTER | INSTEAD OF }
{ ddl_event [OR ddl_event]... | database_event [OR database_event]...}
ON { [schema.] SCHEMA | DATABASE }
--trigger_ordering_clause
[ { FOLLOWS | PRECEDES } [ schmema.] trigger [ , [ schmema.] trigger ]... ]
--trigger_body
{ plsql_block | CALL routine_clause }

Click HERE for complete syntax and the usage for creating a trigger.

Trigger Examples

1. Simple DML Triggers
• BEFORE statement trigger
This example shows the basic syntax for a BEFORE statement trigger. You would write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements could be issued.

CREATE TRIGGER schema.trigger_name 
    BEFORE 
    DELETE OR INSERT OR UPDATE 
    ON schema.table_name 
       pl/sql_block
       
-- Another partial BEFORE row trigger Example      

CREATE TRIGGER hr.salary_check
      BEFORE INSERT OR UPDATE OF salary, job_id ON hr.employees
      FOR EACH ROW
         WHEN (new.job_id <> 'AD_VP')
      pl/sql_block
      

• Trigger Uses Conditional Predicates to Detect Triggering Statement
Creates a DML trigger that uses conditional predicates to determine which of its four possible triggering statements fired it.

CREATE OR REPLACE TRIGGER t
  BEFORE
    INSERT OR
    UPDATE OF salary, department_id OR
    DELETE
  ON employees
BEGIN
  CASE
    WHEN INSERTING THEN
      DBMS_OUTPUT.PUT_LINE('Inserting');
    WHEN UPDATING('salary') THEN
      DBMS_OUTPUT.PUT_LINE('Updating salary');
    WHEN UPDATING('department_id') THEN
      DBMS_OUTPUT.PUT_LINE('Updating department ID');
    WHEN DELETING THEN
      DBMS_OUTPUT.PUT_LINE('Deleting');
  END CASE;
END;
/

• Trigger Logs Changes to EMPLOYEES.SALARY
Creates a log table and a trigger that inserts a row in the log table after any UPDATE statement affects the SALARY column of the EMPLOYEES table, and then updates EMPLOYEES.SALARY and shows the log table.

DROP TABLE Emp_log;
CREATE TABLE Emp_log (
  Emp_id     NUMBER,
  Log_date   DATE,
  New_salary NUMBER,
  Action     VARCHAR2(20));

Create trigger that inserts row in log table after EMPLOYEES.SALARY is updated:

CREATE OR REPLACE TRIGGER log_salary_increase
  AFTER UPDATE OF salary ON employees
  FOR EACH ROW
BEGIN
  INSERT INTO Emp_log (Emp_id, Log_date, New_salary, Action)
  VALUES (:NEW.employee_id, SYSDATE, :NEW.salary, 'New Salary');
END;
/

• Conditional Trigger Prints Salary Change Information
Creates a conditional trigger that prints salary change information whenever a DELETE, INSERT, or UPDATE statement affects the EMPLOYEES table—unless that information is about the President. The database evaluates the WHEN condition for each affected row. If the WHEN condition is TRUE for an affected row, then the trigger fires for that row before the triggering statement runs. If the WHEN condition is not TRUE for an affected row, then trigger does not fire for that row, but the triggering statement still runs.

CREATE OR REPLACE TRIGGER print_salary_changes
  BEFORE DELETE OR INSERT OR UPDATE ON employees
  FOR EACH ROW
  WHEN (NEW.job_id <> 'AD_PRES')  -- do not print information about President
DECLARE
  sal_diff  NUMBER;
BEGIN
  sal_diff  := :NEW.salary  - :OLD.salary;
  DBMS_OUTPUT.PUT(:NEW.last_name || ': ');
  DBMS_OUTPUT.PUT('Old salary = ' || :OLD.salary || ', ');
  DBMS_OUTPUT.PUT('New salary = ' || :NEW.salary || ', ');
  DBMS_OUTPUT.PUT_LINE('Difference: ' || sal_diff);
END;
/

• Trigger Modifies LOB Columns
Creates a trigger that modifies CLOB columns.

DROP TABLE tab1;
CREATE TABLE tab1 (c1 CLOB);
INSERT INTO tab1 VALUES ('
HTML Document Fragment
Some text.');

CREATE OR REPLACE TRIGGER trg1
  BEFORE UPDATE ON tab1
  FOR EACH ROW
BEGIN
  DBMS_OUTPUT.PUT_LINE('Old value of CLOB column: '||:OLD.c1);
  DBMS_OUTPUT.PUT_LINE('Proposed new value of CLOB column: '||:NEW.c1);

  :NEW.c1 := :NEW.c1 || TO_CLOB('
Standard footer paragraph.');

  DBMS_OUTPUT.PUT_LINE('Final value of CLOB column: '||:NEW.c1);
END;
/ 

SET SERVEROUTPUT ON;
UPDATE tab1 SET c1 = '
Different Document Fragment
Different text.';

SELECT * FROM tab1;

• Trigger with REFERENCING Clause
Creates a table with the same name as a correlation name, new, and then creates a trigger on that table. To avoid conflict between the table name and the correlation name, the trigger references the correlation name as Newest.

CREATE TABLE new (
  field1  NUMBER,
  field2  VARCHAR2(20)
);

CREATE OR REPLACE TRIGGER Print_salary_changes
BEFORE UPDATE ON new
REFERENCING new AS Newest
FOR EACH ROW
BEGIN
  :Newest.Field2 := TO_CHAR (:newest.field1);
END;
/

• Trigger References OBJECT_VALUE Pseudocolumn
Creates object table tbl, table tbl_history for logging updates to tbl, and trigger Tbl_Trg. The trigger runs for each row of tb1 that is affected by a DML statement, causing the old and new values of the object t in tbl to be written in tbl_history. The old and new values are :OLD.OBJECT_VALUE and :NEW.OBJECT_VALUE.
Create, populate, and show object table:

CREATE OR REPLACE TYPE t AS OBJECT (n NUMBER, m NUMBER)
/
CREATE TABLE tbl OF t
/
BEGIN
  FOR j IN 1..5 LOOP
    INSERT INTO tbl VALUES (t(j, 0));
  END LOOP;
END;
/
SELECT * FROM tbl ORDER BY n;

• Execution Order of Triggers using FOLLOWS Example
Oracle allows more than one trigger to be created for the same timing point, but it has never guaranteed the execution order of those triggers. The Oracle 11g trigger syntax now includes the FOLLOWS clause to guarantee execution order for triggers defined with the same timing point. The following example creates a table with two triggers for the same timing point.

CREATE TABLE trigger_follows_test (
  id          NUMBER,
  description VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_2
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_2 - Executed');
END;
/

If we insert into the test table, there is no guarantee of the execution order.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (1, 'ONE');
TRIGGER_FOLLOWS_TEST_TRG_1 - Executed
TRIGGER_FOLLOWS_TEST_TRG_2 - Executed

1 row created.

SQL>

We can specify that the TRIGGER_FOLLOWS_TEST_TRG_2 trigger should be executed before the TRIGGER_FOLLOWS_TEST_TRG_1 trigger by recreating the TRIGGER_FOLLOWS_TEST_TRG_1 trigger using the FOLLOWS clause.

CREATE OR REPLACE TRIGGER trigger_follows_test_trg_1
BEFORE INSERT ON trigger_follows_test
FOR EACH ROW
FOLLOWS trigger_follows_test_trg_2
BEGIN
  DBMS_OUTPUT.put_line('TRIGGER_FOLLOWS_TEST_TRG_1 - Executed');
END;
/

Now the TRIGGER_FOLLOWS_TEST_TRG_1 trigger always follows the TRIGGER_FOLLOWS_TEST_TRG_2 trigger.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO trigger_follows_test VALUES (2, 'TWO');
TRIGGER_FOLLOWS_TEST_TRG_2 - Executed
TRIGGER_FOLLOWS_TEST_TRG_1 - Executed

1 row created.

SQL>

• Calling a Procedure in a Trigger Body Example
You could create the salary_check trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure check_sal in the hr schema, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger salary_check as follows:

CREATE TRIGGER salary_check
   BEFORE INSERT OR UPDATE OF salary, job_id ON employees
   FOR EACH ROW
   WHEN (new.job_id <> 'AD_VP')
   --there shoul'nt be any semicolun or forward slash after this statement
   CALL check_sal(:new.job_id, :new.salary, :new.last_name)   

-- OR 

CREATE TRIGGER salary_check
   BEFORE INSERT OR UPDATE OF salary, job_id ON employees
   FOR EACH ROW
   WHEN (new.job_id <> 'AD_VP')
   BEGIN
    check_sal(:new.job_id, :new.salary, :new.last_name);
   END;

The procedure check_sal could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD values in the CALL clause instead of :NEW values.
2. INSTEAD OF DML Triggers
• Creating an INSTEAD OF Trigger Example
In this example, an oe.order_info view is created to display information about customers and their orders:

CREATE 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;

Normally this view would not be updatable, because the primary key of the orders table (order_id) is not unique in the result set of the join view. To make this view updatable, create an INSTEAD OF trigger on the view to process INSERT statements directed to the view.

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)
   VALUES (
     :new.order_id,
     :new.order_date,
     :new.customer_id);
   EXCEPTION
     WHEN duplicate_info THEN
       RAISE_APPLICATION_ERROR (
         num=> -20107,
         msg=> 'Duplicate customer or order ID');
   END order_info_insert;
/

You can now insert into both base tables through the view (as long as all NOT NULL columns receive values).
3. Compound DML Triggers
• Compound Trigger Example
The following code creates a test table and a compound trigger that fires for each timing point associated with insert, update and delete statements. The triggering actions are logged in a PL/SQL table defined in the global declaration section. The final timing point for each statement prints out the content of the PL/SQL table to show that the variable state has been maintained throughout the lifetime of the statement.

CREATE TABLE compound_trigger_test (
  id           NUMBER,
  description  VARCHAR2(50)
);

CREATE OR REPLACE TRIGGER compound_trigger_test_trg
  FOR INSERT OR UPDATE OR DELETE ON compound_trigger_test
    COMPOUND TRIGGER

  -- Global declaration.
  TYPE t_tab IS TABLE OF VARCHAR2(50);
  l_tab t_tab := t_tab();

  BEFORE STATEMENT IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE STATEMENT - DELETE';
    END CASE;
  END BEFORE STATEMENT;

  BEFORE EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - INSERT (new.id=' || :new.id || ')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'BEFORE EACH ROW - DELETE (old.id=' || :old.id || ')';
    END CASE;
  END BEFORE EACH ROW;

  AFTER EACH ROW IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - INSERT (new.id=' || :new.id || ')';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - UPDATE (new.id=' || :new.id || ' old.id=' || :old.id || ')';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER EACH ROW - DELETE (old.id=' || :old.id || ')';
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    l_tab.extend;
    CASE
      WHEN INSERTING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - INSERT';
      WHEN UPDATING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - UPDATE';
      WHEN DELETING THEN
        l_tab(l_tab.last) := 'AFTER STATEMENT - DELETE';
    END CASE;
    
    FOR i IN l_tab.first .. l_tab.last LOOP
      DBMS_OUTPUT.put_line(l_tab(i));
    END LOOP;
    l_tab.delete;
  END AFTER STATEMENT;

END compound_trigger_test_trg;
/

By issuing several insert, update and delete statements against the test table we can see that the compound trigger is working as expected.

SQL> SET SERVEROUTPUT ON
SQL> INSERT INTO compound_trigger_test VALUES (1, 'ONE');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=1)
AFTER EACH ROW - INSERT (new.id=1)
AFTER STATEMENT - INSERT

1 row created.

SQL> INSERT INTO compound_trigger_test VALUES (2, 'TWO');
BEFORE STATEMENT - INSERT
BEFORE EACH ROW - INSERT (new.id=2)
AFTER EACH ROW - INSERT (new.id=2)
AFTER STATEMENT - INSERT

1 row created.

SQL> UPDATE compound_trigger_test SET id = id;
BEFORE STATEMENT - UPDATE
BEFORE EACH ROW - UPDATE (new.id=2 old.id=2)
AFTER EACH ROW - UPDATE (new.id=2 old.id=2)
BEFORE EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER EACH ROW - UPDATE (new.id=1 old.id=1)
AFTER STATEMENT - UPDATE

2 rows updated.

SQL> DELETE FROM compound_trigger_test;
BEFORE STATEMENT - DELETE
BEFORE EACH ROW - DELETE (old.id=2)
AFTER EACH ROW - DELETE (old.id=2)
BEFORE EACH ROW - DELETE (old.id=1)
AFTER EACH ROW - DELETE (old.id=1)
AFTER STATEMENT - DELETE

2 rows deleted.

SQL>

• Compound Trigger Logs Changes to One Table in Another Table
You want to log every change to hr.employees.salary in a new table, employee_salaries. A single UPDATE statement updates many rows of the table hr.employees; therefore, bulk-inserting rows into employee.salaries is more efficient than inserting them individually.

Define a compound trigger on updates of the table hr.employees, as in Example 9-9. You do not need a BEFORE STATEMENT section to initialize idx or salaries, because they are state variables, which are initialized each time the trigger fires (even when the triggering statement is interrupted and restarted).

CREATE TABLE employee_salaries (
  employee_id NUMBER NOT NULL,
  change_date DATE   NOT NULL,
  salary NUMBER(8,2) NOT NULL,
  CONSTRAINT pk_employee_salaries PRIMARY KEY (employee_id, change_date),
  CONSTRAINT fk_employee_salaries FOREIGN KEY (employee_id)
    REFERENCES employees (employee_id)
      ON DELETE CASCADE)
/
CREATE OR REPLACE TRIGGER maintain_employee_salaries
  FOR UPDATE OF salary ON employees
    COMPOUND TRIGGER

-- Declarative Part:
-- Choose small threshhold value to show how example works:
  threshhold CONSTANT SIMPLE_INTEGER := 7;

  TYPE salaries_t IS TABLE OF employee_salaries%ROWTYPE INDEX BY SIMPLE_INTEGER;
  salaries  salaries_t;
  idx       SIMPLE_INTEGER := 0;

  PROCEDURE flush_array IS
    n CONSTANT SIMPLE_INTEGER := salaries.count();
  BEGIN
    FORALL j IN 1..n
      INSERT INTO employee_salaries VALUES salaries(j);
    salaries.delete();
    idx := 0;
    DBMS_OUTPUT.PUT_LINE('Flushed ' || n || ' rows');
  END flush_array;

  -- AFTER EACH ROW Section:

  AFTER EACH ROW IS
  BEGIN
    idx := idx + 1;
    salaries(idx).employee_id := :NEW.employee_id;
    salaries(idx).change_date := SYSDATE();
    salaries(idx).salary := :NEW.salary;
    IF idx >= threshhold THEN
      flush_array();
    END IF;
  END AFTER EACH ROW;

  -- AFTER STATEMENT Section:

  AFTER STATEMENT IS
  BEGIN
    flush_array();
  END AFTER STATEMENT;
END maintain_employee_salaries;
/
/* Increase salary of every employee in department 50 by 10%: */

UPDATE employees
  SET salary = salary * 1.1
  WHERE department_id = 50
/

/* Wait two seconds: */

BEGIN
  DBMS_LOCK.SLEEP(2);    --To run this line, you must have the EXECUTE privilege on the package DBMS_LOCK.
END;
/

/* Increase salary of every employee in department 50 by 5%: */

UPDATE employees
  SET salary = salary * 1.05
  WHERE department_id = 50
/

4. Triggers for Ensuring Referential Integrity
• UPDATE and DELETE SET NULL Trigger for Parent Table
The trigger in enforces the UPDATE and DELETE SET NULL referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_set_null
  AFTER DELETE OR UPDATE OF Deptno ON dept
  FOR EACH ROW

  -- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
  -- set all corresponding dependent foreign key values in emp to NULL:

BEGIN
  IF UPDATING AND :OLD.Deptno != :NEW.Deptno OR DELETING THEN
    UPDATE emp SET emp.Deptno = NULL
    WHERE emp.Deptno = :OLD.Deptno;
  END IF;
END;
/

• DELETE CASCADE Trigger for Parent Table
The trigger in enforces the DELETE CASCADE referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_del_cascade
  AFTER DELETE ON dept
  FOR EACH ROW

  -- Before row is deleted from dept,
  -- delete all rows from emp table whose DEPTNO is same as
  -- DEPTNO being deleted from dept table:

BEGIN
  DELETE FROM emp
  WHERE emp.Deptno = :OLD.Deptno;
END;
/

• Foreign Key Trigger for Child Table
The trigger ensures that before an INSERT or UPDATE statement affects a foreign key value, the corresponding value exists in the parent key. The exception ORA-04091 (mutating-table error) allows the trigger emp_dept_check to be used with the UPDATE_SET_DEFAULT and UPDATE_CASCADE triggers. This exception is unnecessary if the trigger emp_dept_check is used alone.

CREATE OR REPLACE TRIGGER emp_dept_check
  BEFORE INSERT OR UPDATE OF Deptno ON emp
  FOR EACH ROW WHEN (NEW.Deptno IS NOT NULL)

  -- Before row is inserted or DEPTNO is updated in emp table,
  -- fire this trigger to verify that new foreign key value (DEPTNO)
  -- is present in dept table.
DECLARE
  Dummy               INTEGER;  -- Use for cursor fetch
  Invalid_department  EXCEPTION;
  Valid_department    EXCEPTION;
  Mutating_table      EXCEPTION;
  PRAGMA EXCEPTION_INIT (Mutating_table, -4091);

  -- Cursor used to verify parent key value exists.
  -- If present, lock parent key's row so it cannot be deleted
  -- by another transaction until this transaction is
  -- committed or rolled back.

  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM dept
    WHERE Deptno = Dn
    FOR UPDATE OF Deptno;
BEGIN
  OPEN Dummy_cursor (:NEW.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- Verify parent key.
  -- If not found, raise user-specified error code and message.
  -- If found, close cursor before allowing triggering statement to complete:

  IF Dummy_cursor%NOTFOUND THEN
    RAISE Invalid_department;
  ELSE
    RAISE valid_department;
  END IF;
  CLOSE Dummy_cursor;
EXCEPTION
  WHEN Invalid_department THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20000, 'Invalid Department'
      || ' Number' || TO_CHAR(:NEW.deptno));
  WHEN Valid_department THEN
    CLOSE Dummy_cursor;
  WHEN Mutating_table THEN
    NULL;
END;
/

• UPDATE and DELETE RESTRICT Trigger for Parent Table
The trigger enforces the UPDATE and DELETE RESTRICT referential action on the primary key of the dept table.

CREATE OR REPLACE TRIGGER dept_restrict
  BEFORE DELETE OR UPDATE OF Deptno ON dept
  FOR EACH ROW

  -- Before row is deleted from dept or primary key (DEPTNO) of dept is updated,
  -- check for dependent foreign key values in emp;
  -- if any are found, roll back.

DECLARE
  Dummy                  INTEGER;  -- Use for cursor fetch
  Employees_present      EXCEPTION;
  employees_not_present  EXCEPTION;

  -- Cursor used to check for dependent foreign key values.
  CURSOR Dummy_cursor (Dn NUMBER) IS
    SELECT Deptno FROM emp WHERE Deptno = Dn;

BEGIN
  OPEN Dummy_cursor (:OLD.Deptno);
  FETCH Dummy_cursor INTO Dummy;

  -- If dependent foreign key is found, raise user-specified
  -- error code and message. If not found, close cursor
  -- before allowing triggering statement to complete.

  IF Dummy_cursor%FOUND THEN
    RAISE Employees_present;     -- Dependent rows exist
  ELSE
    RAISE Employees_not_present; -- No dependent rows exist
  END IF;
  CLOSE Dummy_cursor;

EXCEPTION
  WHEN Employees_present THEN
    CLOSE Dummy_cursor;
    Raise_application_error(-20001, 'Employees Present in'
      || ' Department ' || TO_CHAR(:OLD.DEPTNO));
  WHEN Employees_not_present THEN
    CLOSE Dummy_cursor;
END;
/

5. System Triggers
• Creating a SCHEMA Trigger
The following example creates a BEFORE statement trigger on the sample schema hr. When a user connected as hr attempts to drop a database object, the database fires the trigger before dropping the object:

CREATE OR REPLACE TRIGGER drop_trigger 
   BEFORE DROP ON hr.SCHEMA 
   BEGIN
      RAISE_APPLICATION_ERROR (
         num => -20000,
         msg => 'Cannot drop object');
   END;
/

• Creating a Database Event Trigger
This example shows the basic syntax for a trigger to log all errors. The hypothetical PL/SQL block does some special processing for a particular error (invalid logon, error number 1017). This trigger is an AFTER statement trigger, so it is fired after an unsuccessful statement execution, such as unsuccessful logon.

CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE 
   BEGIN
      IF (IS_SERVERERROR (1017)) THEN
         NULL; --
      ELSE
         NULL; -- 
      END IF;
   END;

• Creating a DDL Trigger Examples
This example creates an AFTER statement trigger on any DDL statement CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.

CREATE TRIGGER audit_db_object AFTER CREATE
   ON SCHEMA
      pl/sql_block
      

• Trigger Monitors Logons
The trigger runs the procedure check_user after a user logs onto the database.

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 Trigger on Schema
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.

CREATE OR REPLACE TRIGGER t
  INSTEAD OF CREATE ON SCHEMA
  BEGIN
    EXECUTE IMMEDIATE 'CREATE TABLE T (n NUMBER, m NUMBER)';
  END;
/

6. Miscellaneous Trigger Examples
• Trigger Invokes Java Subprogram
Triggers can invoke subprograms written in PL/SQL, C, and Java. The trigger in Example 9-9 invokes a PL/SQL subprogram. The trigger in Example 9-23 invokes a Java subprogram.

CREATE OR REPLACE PROCEDURE Before_delete (Id IN NUMBER, Ename VARCHAR2)
IS LANGUAGE Java
name 'thjvTriggers.beforeDelete (oracle.sql.NUMBER, oracle.sql.CHAR)';

CREATE OR REPLACE TRIGGER Pre_del_trigger BEFORE DELETE ON Tab 
FOR EACH ROW
CALL Before_delete (:OLD.Id, :OLD.Ename)
/

The corresponding Java file is thjvTriggers.java:

import java.sql.*
import java.io.*
import oracle.sql.*
import oracle.oracore.*
public class thjvTriggers
{
public static void
beforeDelete (NUMBER old_id, CHAR old_name)
Throws SQLException, CoreException
   {
   Connection conn = JDBCConnection.defaultConnection();
   Statement stmt = conn.CreateStatement();
   String sql = "insert into logtab values
   ("+ old_id.intValue() +", '"+ old_ename.toString() + ", BEFORE DELETE');
   stmt.executeUpdate (sql);
   stmt.close();
   return;
   }
}

A subprogram invoked by a trigger cannot run transaction control statements, because the subprogram runs in the context of the trigger body.

If a trigger invokes an invoker rights (IR) subprogram, then the user who created the trigger, not the user who ran the triggering statement, is considered to be the current user. For information about IR subprograms, see "Invoker's Rights and Definer's Rights (AUTHID Property)".

If a trigger invokes a remote subprogram, and a time stamp or signature mismatch is found during execution of the trigger, then the remote subprogram does not run and the trigger is invalidated.

Related Blogs-

1. Oracle Database Introduction
2. Sql Blogs Listing
3. Oracle Blogs Listing

References-

1. https://docs.oracle.com/cd/B19306_01/server.102/b14220/triggers.htm
2. http://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm#LNPLS01374
3. https://docs.oracle.com/cloud/latest/db112/LNPLS/create_trigger.htm#LNPLS01374
4. https://oracle-base.com/articles/11g/trigger-enhancements-11gr1

Subscribe to Java 4 Brains by Email