What is a Trigger?

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table. A trigger is triggered automatically when an associated DML statement is executed.

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]

WHEN (condition)

DECLARE

   Declaration-statements

BEGIN

   Executable-statements

EXCEPTION

   Exception-handling-statements

END;

· CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.

· {BEFORE | AFTER | INSTEAD OF } - This clause indicates at what time should the trigger get fired. i.e for example: before or after updating a table. INSTEAD OF is used to create a trigger on a view. before and after cannot be used to create a trigger on a view.

Instead-of Triggers:

Instead-of triggers can be defined on operations performed on views only. When you define a instead of trigger on an operation on a view, the trigger code will be executed instead of the operation that fired it. This type of triggers can only be row level.

· {INSERT [OR] | UPDATE [OR] | DELETE} - This clause determines the triggering event. More than one triggering events can be used together separated by OR keyword. The trigger gets fired at all the specified triggering event.

· [OF col_name] - This clause is used with update triggers. This clause is used when you want to trigger an event only when a specific column is updated.

· CREATE [OR REPLACE ] TRIGGER trigger_name - This clause creates a trigger with the given name or overwrites an existing trigger with the same name.

· [ON table_name] - This clause identifies the name of the table or view to which the trigger is associated.

· [REFERENCING OLD AS o NEW AS n] - This clause is used to reference the old and new values of the data being changed. By default, you reference the values as :old.column_name or :new.column_name. The reference names can also be changed from old (or new) to any other user-defined name. You cannot reference old values when inserting a record, or new values when deleting a record, because they do not exist.

· [FOR EACH ROW] - This clause is used to determine whether a trigger must fire when each row gets affected ( i.e. a Row Level Trigger) or just once when the entire sql statement is executed(i.e.statement level Trigger).

· WHEN (condition) - This clause is valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.

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;

NAME Type

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

TRIGGER_NAME                 VARCHAR2(30)
TRIGGER_TYPE                  VARCHAR2(16)
TRIGGER_EVENT                VARCHAR2(75)
TABLE_OWNER                 VARCHAR2(30)
BASE_OBJECT_TYPE           VARCHAR2(16)
TABLE_NAME                     VARCHAR2(30)
COLUMN_NAME                 VARCHAR2(4000)
REFERENCING_NAMES        VARCHAR2(128)
WHEN_CLAUSE                 VARCHAR2(4000)
STATUS                            VARCHAR2(8)
DESCRIPTION                    VARCHAR2(4000)
ACTION_TYPE                   VARCHAR2(11)
TRIGGER_BODY                 LONG

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

SELECT * FROM user_triggers WHERE trigger_name = 'Before_Update_Stat_product';

The above sql query provides the header and body of the trigger 'Before_Update_Stat_product'.

You can drop a trigger using the following command.

DROP TRIGGER trigger_name;

Check the status of the trigger

SQL> SELECT trigger_name, status FROM user_triggers;

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 situtation 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.

Use cursor in trigger

CREATE OR REPLACE TRIGGER bi_order
   BEFORE INSERT
   ON ord
   REFERENCING OLD AS OLD NEW AS NEW
   FOR EACH ROW
   WHEN (NEW.payment_type = 'CREDIT')
   DECLARE
      CURSOR cur_check_customer IS
         SELECT 'x'
         FROM   customer
         WHERE  customer_id = :NEW.customer_id
         AND    credit_rating = 'POOR';
      lv_temp_txt          VARCHAR2(1);
      lv_poor_credit_excep EXCEPTION;
   BEGIN
      OPEN cur_check_customer;
      FETCH cur_check_customer INTO lv_temp_txt;
      IF (cur_check_customer%FOUND) THEN
         CLOSE cur_check_customer;
         RAISE lv_poor_credit_excep;
      ELSE
         CLOSE cur_check_customer;
      END IF;
   EXCEPTION
      WHEN lv_poor_credit_excep THEN
         RAISE_APPLICATION_ERROR(-20111, 'Cannot process CREDIT ' ||
            'order for a customer with a POOR credit rating.');
      WHEN OTHERS THEN
         RAISE_APPLICATION_ERROR(-20122, 'Unhandled error occurred in' ||
            ' BI_ORDER trigger for order#:' || TO_CHAR(:NEW.ORDER_ID));
   END bi_order;
   /

INSERTING, DELETING and UPDATING Predicates

CREATE OR REPLACE TRIGGER LogRSChanges
      BEFORE INSERT OR DELETE OR UPDATE ON employee
      FOR EACH ROW
    DECLARE
      v_ChangeType CHAR(1);
    BEGIN
      /* Use 'I' for an INSERT, 'D' for DELETE, and 'U' for UPDATE. */
      IF INSERTING THEN
        v_ChangeType := 'I';
    ELSIF UPDATING THEN
      v_ChangeType := 'U';
    ELSE
      v_ChangeType := 'D';
    END IF;

    DBMS_OUTPUT.put_line(v_ChangeType ||' '|| USER ||' ' ||SYSDATE);
  END LogRSChanges;
  /

Use of WHEN condition

CREATE OR REPLACE TRIGGER author_trig
      AFTER UPDATE OF first_name
      ON authors
      FOR EACH ROW
   WHEN (OLD.first_name != NEW.first_name)
   BEGIN
      DBMS_OUTPUT.PUT_LINE('First Name '
                           ||:OLD.first_name
                           ||' has change to '
                           ||:NEW.first_name);
   END;
   /

SQL> UPDATE authors SET first_name = 'Ronald' WHERE first_name = 'Ron';
First Name Ron has change to Ronald

1 row updated.

Creating a SCHEMA Trigger: Example

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

System triggers

There are a number of events where you can set system triggers such as

ON LOGON, ON LOGOFF, ON STARTUP, ON DROP, ON TRUNCATE, and so on.

You can even track when any DDL command (CREATE, DROP, ALTER, and so on) was executed in the database.

You may place system triggers at the database level or schema level.

At the database level, triggers fire for each event for all users.

At the schema level, triggers fire for each event for a specific user.

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

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

STARTUP
Specify STARTUP to fire the trigger whenever the database is opened.

SHUTDOWN
Specify SHUTDOWN to fire the trigger whenever an instance of the database is shut down.

SUSPEND
Specify SUSPEND to fire the trigger whenever a server error causes a transaction to be suspended.

Notes:

Only AFTER triggers are relevant for LOGON, STARTUP, SERVERERROR, and SUSPEND.
Only BEFORE triggers are relevant for LOGOFF and SHUTDOWN.
AFTER STARTUP and BEFORE SHUTDOWN triggers apply only to DATABASE.

System triggers Examples.

CREATE TABLE connection_audit (
login_date DATE,
user_name  VARCHAR2(30));
CREATE OR REPLACE PROCEDURE logproc ISBEGIN
  INSERT INTO connection_audit
(login_date, user_name)
  VALUES
(SYSDATE, USER);END logproc;
/
CREATE OR REPLACE TRIGGER logintrig
AFTER LOGON ON DATABASE
CALL logproc
/

conn sh/sh
conn scott/tiger
conn uwclass/uwclassSELECT *
FROM connection_audit;
drop trigger logintrig;

-- trigger to trap successful logons

/*
other errors that could be trapped include:ORA-01004 - default username feature not supportedORA-01005 - null password givenORA-01035 - Oracle only available to users with restricted session privORA-01045 - create session privilege not granted
*/

System triggers Examples.

CREATE OR REPLACE TRIGGER logon_audit
AFTER LOGON ON DATABASE BEGIN
  INSERT INTO connection_audit
(login_date, user_name)
  VALUES
(SYSDATE, USER);END logon_audit;
/

conn scott/tiger
conn sh/sh
conn / as sysdba
conn uwclass/uwclassSELECT *FROM connection_audit;

-- trigger to trap unsuccessful logonsCREATE OR REPLACE TRIGGER logon_failures
AFTER SERVERERROR
ON DATABASEBEGIN
  IF (IS_SERVERERROR(1017)) THEN
    INSERT INTO connection_audit
    (login_date, user_name)
    VALUES
    (SYSDATE, 'ORA-1017');
  END IF;END logon_failures;
/

conn scott/tigre
conn abc/def
conn test/test

conn uwclass/uwclassSELECT *FROM connection_audit;

DML Triggers

BEFORE INSERT Trigger

A BEFORE INSERT Trigger means that Oracle will fire this trigger before the INSERT operation is executed.

Restrictions

· You can not create a BEFORE trigger on a view.

· You can update the :NEW values.