PL/SQL - Triggers | AskHareesh Blog | Oracle Apps | Fitness by Hareesh Pothuguntla

Triggers :

1. What is a Trigger?

A database trigger is a stored procedure associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be called after the event, to record it, or take some follow-up action. The trigger can be called before the event, to prevent erroneous operations or fix new data so that it conforms to business rules. The executable part of a trigger can contain procedural statements and SQL data manipulation statements

2. When you need a trigger?

You can write triggers that fire whenever one of the following operations occurs:

· DML( Data Manipulation Language) statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user

· DDL (Data Definition Language) statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database

· Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database

Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.

3. How Triggers Are Used?

Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:

· Automatically generate derived column values

· Prevent invalid transactionsf

· Enforce complex security authorizations

· Enforce referential integrity across nodes in a distributed database

· Enforce complex business rules

· Provide transparent event logging

· Provide 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

4. What does trigger Consists?

A trigger has three basic parts:

· A triggering event or statement

· A trigger restriction

· A trigger action

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

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:

new.parts_on_hand < new.reorder_point

Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.

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 the following events occur:

· A triggering statement is issued.

· 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

If the triggers are row triggers, the statements in a trigger action have access to column values of the row being processed by the trigger. Correlation names provide access to the old and new values for each column.

Basic Trigger Syntax

Below is the syntax for creating a trigger in Oracle (which differs slightly from standard SQL syntax):

CREATE [OR REPLACE] TRIGGER <trigger_name>

{BEFORE|AFTER} {INSERT|DELETE|UPDATE} ON <table_name>

[REFERENCING [NEW AS <new_row_name>] [OLD AS <old_row_name>]]

[FOR EACH ROW [WHEN (<trigger_condition>)]] <trigger_body>

Some important points to note on Triggers:

You can create only BEFORE and AFTER triggers for tables. (INSTEAD OF triggers are only available for views; typically they are used to implement view updates.)

You may specify up to three triggering events using the keyword OR. Furthermore, UPDATE can be optionally followed by the keyword OF and a list of attribute(s) in <table_name>. If present, the OF clause defines the event to be only an update of the attribute(s) listed after OF. Here are some examples:

·             ... INSERT ON R ...

·             ... INSERT OR DELETE OR UPDATE ON R ...

·

    ... UPDATE OF A, B OR INSERT ON R ...

If FOR EACH ROW option is specified, the trigger is row-level; otherwise, the trigger is statement-level.

Only for row-level triggers:

The special variables NEW and OLD are available to refer to new and old tuples respectively. Note: In the trigger body, NEW and OLD must be preceded by a colon (":"), but in the WHEN clause, they do not have a preceding colon! See example below.
The REFERENCING clause can be used to assign aliases to the variables NEW and OLD.
A trigger restriction can be specified in the WHEN clause, enclosed by parentheses. The trigger restriction is a SQL condition that must be satisfied in order for Oracle to fire the trigger. This condition cannot contain subqueries. Without the WHEN clause, the trigger is fired for each row.

· <trigger_body> is a PL/SQL block, rather than sequence of SQL statements. Oracle has placed certain restrictions on what you can do in <trigger_body>, in order to avoid situations where one trigger performs an action that triggers a second trigger, which then triggers a third, and so on, which could potentially create an infinite loop.

· The restrictions on <trigger_body> include:

o You cannot modify the same relation whose modification is the event triggering the trigger.

o You cannot modify a relation connected to the triggering relation by another constraint such as a foreign-key constrain

5. What are the types of trigger?

Insert Triggers

Before Insert trigger

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.
The syntax for the BEFORE INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
Trigger_name is the name of the trigger to create.
Restrictions:

You can not create a BEFORE trigger on a view.
You can update the : NEW values.
You can not update the : OLD values.

After Insert trigger

An AFTER INSERT Trigger means that Oracle will fire this trigger after the INSERT operation is executed.
The syntax for an AFTER INSERT Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER INSERT
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
For example: For the table order2

CREATE TABLE Orders2

 (order_id number(5),

  quantity number(4),

  cost_per_item number (6,2),

  totoal_cost number(8,2));



We could then create an AFTER INSERT trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_insert

AFTER INSERT

ON orders2

FOR EACH ROW

DECLARE

v_username varchar2(10);

BEGIN

-- Find username of person performing the INSERT into the table

SELECT user INTO v_username

FROM dual;

-- Insert record into audit table

INSERT INTO orders_audit

( order_id,

quantity,

cost_per_item,

total_cost,

username )

VALUES

( :new.order_id,

:new.quantity,

:new.cost_per_item,

:new.total_cost,

v_username );

END;

Update Triggers

Before update Trigger

A BEFORE UPDATE Trigger means that Oracle will fire this trigger before the UPDATE operation is executed.
The syntax for BEFORE UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE UPDATE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
Restrictions:

You can not create a BEFORE trigger on a view.
You can update the :NEW values.
You can not update the :OLD values.

Example: - Create a table as follows:

Create table Orders2

 ( order-id number(5),

  quantity number(4),

  cost_per_item number(6,2),

  total_cost number(8,2),

  updated_date date,

  updated_by varchar2(10));

Screen shot showing the table has been created.

We could then create a BEFORE UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_before_update

BEFORE UPDATE

    ON orders

    FOR EACH ROW

DECLARE

    v_username varchar2(10);

BEGIN

    -- Find username of person performing UPDATE on the table

    SELECT user INTO v_username

    FROM dual;

    -- Update updated_date field to current system date

    :new.updated_date := sysdate;

    -- Update updated_by field to the username of the person performing the UPDATE

    :new.updated_by := v_username;

END;

After update Trigger

An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
    ON table_name
    [FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
Trigger name is name of the trigger
Restrictions:

You can not create an AFTER trigger on a view.
You can not update the : NEW values.
You can not update the : OLD values.

For example:
If you had a table created as follows:

Create table Orders2

 ( order_id number(5),

   quantity number (4),

   cost_per_item number(6,2),

   total_cost number(8,2));

We could then create an AFTER UPDATE trigger as follows:

CREATE OR REPLACE TRIGGER orders_after_update

AFTER UPDATE ON orders2 FOR EACH ROW

DECLARE

    v_username varchar2(10);

BEGIN

    SELECT user INTO v_username

    FROM dual;

    -- Insert record into audit table

    INSERT INTO orders_audit

     ( order_id,

       quantity_before,

       quantity_after,

       username )

    VALUES

     ( :new.order_id,

       :old.quantity,

       :new.quantity,

       v_username );

END;

Delete Triggers

Before Delete Trigger

A BEFORE DELETE Trigger means that Oracle will fire this trigger before the DELETE operation is executed.
The syntax for a BEFORE DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
BEFORE DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:

You can not create a BEFORE trigger on a view.
You can update the : NEW values.
You can not update the : OLD values.

For example:
If you had a table created as follows:

Create table Orders2

 ( order_id number(5),

   quantity number (4),

   cost_per_item number(6,2),

   total_cost number(8,2));

We could then create a BEFORE DELETE trigger as follows:
CREATE OR REPLACE TRIGGER orders_before_delete
BEFORE DELETE
    ON orders
    FOR EACH ROW
DECLARE
    v_username   varchar2 (10);
BEGIN
    -- Find username of person performing the DELETE on the table
    SELECT user INTO v_username
    FROM dual;
    -- Insert record into audit table
    INSERT INTO orders_audit
     ( order_id,
       quantity,
       cost_per_item,
       total_cost,
       delete_date,
       deleted_by )
    VALUES
     ( :old.order_id,
       :old.quantity,
       :old.cost_per_item,
       :old.total_cost,
       sysdate,
       v_username );
END;

After Delete Trigger

An AFTER DELETE Trigger means that Oracle will fire this trigger after the DELETE operation is executed.
The syntax for an AFTER DELETE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER DELETE
    ON table_name
    [ FOR EACH ROW ]
DECLARE
    -- variable declarations
BEGIN
    -- trigger code
EXCEPTION
    WHEN ...
    -- exception handling
END;
Trigger_name is the name of the trigger to create.
Restrictions:

You can not create an AFTER trigger on a view.
You can not update the : NEW values.

You can not update the: OLD values.

For example:
If you had a table created as follows:

Create   table Orders

( order_id number(5),

 quantity number(4),

 cost_per_item number(6,2),

 total_cost number (8,2));

A DELETE UPDATE trigger can be create as follows:

CREATE OR REPLACE TRIGGER orders_after_delete

AFTER DELETE

    ON orders

    FOR EACH ROW

DECLARE

    v_username varchar2 (10);

BEGIN

    -- Find username of person performing the DELETE on the table

    SELECT user INTO v_username

    FROM dual;

    -- Insert record into audit table

    INSERT INTO orders_audit

     ( order_id,

       quantity,

       cost_per_item,

       total_cost,

       delete_date,

       deleted_by)

    VALUES

     ( :old.order_id,

       :old.quantity,

       :old.cost_per_item,

       :old.total_cost,

       sysdate,

       v_username );

END;

Drop Triggers

Drop a Trigger

The syntax for a dropping a Trigger is:
DROP TRIGGER trigger_name;

Mutating Error

The Mutating table error is a well-known problem encountered in development; most developers have come across this error.

ORA-04091: table <tablename> is mutating,

               trigger/function may not see it

The basic reason for this error is the way Oracle manages a read consistent view of data. The error is encountered when a row-level trigger accesses the same table on which it is based, while executing. The table is said to be mutating. Mutation will not occur if a single record is inserted in the table (using VALUES clause). If bulk insertion is done or data is inserted from another table mutation will occur.
The mutating error is not only encountered during queries, but also for insert, updates and deletes present in the trigger. Below is a table that explains the various transaction scenarios that involves a trigger and whether it is prone to generate the mutating error. The OPERATION column explains the DML activity being performed and the TYPE column lists the type of trigger created and the execution level.

Case 1: When Trigger on table refers the same table:

-----------------------------------------------------------------

OPERATION       TYPE                        MUTATING?

-----------------------------------------------------------------

insert          before/statement-level      No

insert          after/statement-level       No

update          before/statement-level      No

update          after/statement-level       No

delete          before/statement-level      No

delete          after/statement-level       No

insert          before/row-level            Single row   Multi-row

                                            No           Yes

insert          after/row-level             Yes

update          before/row-level            Yes

update          after/row-level             Yes

delete          before/row-level            Yes

delete          after/row-level             Yes

-----------------------------------------------------------------

A very simple example is given below.

SQL> create table am27

  2  (col1  number,

  3   col2  varchar2(30));

Table created.

SQL> create or replace trigger am27_trg

  2  before insert or update or delete

  3  on am27

  4  for each row

  5  declare

  6    l_chk pls_integer;

  7  begin

  8    select count(1)

  9    into   l_chk

 10    from   am27;

 11    -- more processing...

 12  end;

Trigger created.

SQL> insert into am27 values (1, 'testing');

1 row created.

SQL> update am27

  2  set    col1 = 2;

update am27

ERROR at line 1:

ORA-04091: table SYSTEM.AM27 is mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.AM27_TRG", line 4

ORA-04088: error during execution of trigger 'SYSTEM.AM27_TRG'

In the above example, as table AM27 is being queried in the trigger AM27_TRG that is based on the same table, a mutating error is received.
It is also possible for ORA-4091 to be encountered when querying a table other than the table on which the trigger is based! This happens when a foreign key reference is present with an on-delete-cascade option. A row level trigger on the master table will mutate if the detail table is being referred to in the trigger, for a delete transaction. This will only happen if the foreign key on the detail table is created with the on delete cascade option. No mutation occurs if the master table is being referred in a trigger on the detail table.
There is one odd case where mutation may occur when some other table in the trigger is referred to; below is an example of such a condition.
AM10 is a master table. AM10_DTL is the detail table that is related to the master table with the on-delete-cascade option. AM10_BEF_TRG is created on the master table that queries the detail table for some information. Issuing a delete on the master table results in the mutation error.

SQL> create table am10

  2  (col1 number, col2 varchar2(10));

Table created.

SQL> create table am10_dtl

  2  (col1  number,

  3   col2  varchar2(10));

Table created.

SQL> alter table am10 add primary key (col1);

Table altered.

SQL> alter table am10_dtl add foreign key (col1) references am10(col1) on delete cascade;

Table altered.

SQL> create or replace trigger am10_bef_trg

  2  before insert or update or delete on am10

  3  for each row

  4  declare

  5    l_chk pls_integer;

  6  begin

  7    select 1

  8    into   l_chk

  9    from   am10_dtl

 10    where  col1 = :new.col1;

 11    dbms_output.put_line('ok');

 12  exception

 13    when no_data_found then

 14     dbms_output.put_line('no dtl recs');

 15  end;

 16  /

Trigger created.

SQL> insert into am10 values (1, 'amar');

err

1 row created.

SQL> insert into am10 values (2, 'chk');

err

1 row created.

SQL> insert into  am10_dtl values(1, 'cooler');

1 row created.

SQL> insert into am10_dtl values (2, 'validator');

1 row created.

SQL> delete from am10 where col1= 1;

delete from am10 where col1= 1

ERROR at line 1:

ORA-04091: table SYSTEM.AM10_DTL is mutating, trigger/function may not see it

ORA-06512: at "SYSTEM.AM10_BEF_TRG", line 4

ORA-04088: error during execution of trigger 'SYSTEM.AM10_BEF_TRG'

Solutions for Mutating Error

Here is a technical paper for Oracle Mutating Error.

http://www.procaseconsulting.com/learning/papers/200004%20mutating%20table.pdf

Trigger Cascading

Automatically generate derived column values
Prevent invalid transactions
Enforce complex security authorizations
Enforce referential integrity across nodes in a distributed database
Enforce complex business rules
Provide transparent event logging
Provide 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

Although triggers are useful for customizing a database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application. For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers.

Figure showing the Cascading Triggers….

Text description of cncpt077.gif follows

Database Triggers.

Database trigger are defined at database or schema level and can be fired at certain level database and DDL events. In particular these events occur at database-wide events occur. The DDL events incldue CREATE, DROP and ALTER

The following are the database events include as follows.

· STARTUP : - Fires when the database is opened.

· SHUTDOWN :- Fires when the database is shutdown normally.

· SERVERERROR :- Fires when an Oracle error is raised.

· LOGON :- Fires when an Oracle Session begins.

· LOGOFF. :- Fires when an Oracle Session terminates normally.

The database trigger of intrest here is the 'BEFORE LOFFOFF' trigger. It used to collect summrized session-level wait event data and CPU statistics from the following Views V$SESSION and V$SESSTAT view when sessions log o

CREATE OR REPLACE TRIGGER trigger name

{BEFORE | AFTER } { datbase event} ON {DATBASE |SCHEMA }

DECLARE

varible declarations

BEGIN

/* Save code here */

END

Syntax of Database Trigger

Conclusion

Maintaining a consistent view of the data is an important feature of Oracle. The mutating error conflicts with maintaining a consistent view, therefore, care should be taken to write proper code and avoid such triggers. It is for the developers to write proper logic so that such complications do not arise.

If there is a requirement to update the base table from the row-level trigger, then split the logic across multiple triggers. The required information can be stored in a temporary table, PL/SQL table or package variables when the row-level trigger is executed. A statement-level trigger can then be used to pickup the stored information and apply it to the table.

PL/SQL - Triggers

Trigger Action

Basic Trigger Syntax

Solutions for Mutating Error

Here is a technical paper for Oracle Mutating Error.

http://www.procaseconsulting.com/learning/papers/200004%20mutating%20table.pdf

Trigger Cascading

Figure showing the Cascading Triggers….

Database Triggers.

Database trigger are defined at database or schema level and can be fired at certain level database and DDL events. In particular these events occur at database-wide events occur. The DDL events incldue CREATE, DROP and ALTER

The following are the database events include as follows.

· STARTUP : - Fires when the database is opened.

· SHUTDOWN :- Fires when the database is shutdown normally.

· SERVERERROR :- Fires when an Oracle error is raised.

· LOGON :- Fires when an Oracle Session begins.

· LOGOFF. :- Fires when an Oracle Session terminates normally.

The database trigger of intrest here is the 'BEFORE LOFFOFF' trigger. It used to collect summrized session-level wait event data and CPU statistics from the following Views V$SESSION and V$SESSTAT view when sessions log o

CREATE OR REPLACE TRIGGER trigger name

{BEFORE | AFTER } { datbase event} ON {DATBASE |SCHEMA }

DECLARE

varible declarations

BEGIN

/* Save code here */

END

Syntax of Database Trigger

Conclusion

Complete Tutorial

No comments:

Post a Comment