What is a trigger ?

Triggers are procedures that are stored in the database and are implicitly run, or fired,

when something happens.

Classification tree of triggers ?

Ans: Triggers can be of following types :

DML triggers on tables.

INSTEAD OF triggers on views.

Syst em triggers on DATABASE or SCHEMA: With DATABASE, triggers fire for each event for all users; with SCHEMA,triggers fire for each event for that specific user.

But more convenient way of classifying triggers is on the basis of Events on which they are
fired :

DML statements (DELETE, INSERT, UPDATE)

DDL statements (CREATE, ALTER, DROP)

Database operations (SERVERERROR, LOGON, LOGOFF, STARTUP, SHUTDOWN)

Now Creating triggers : 8 common types of trigger are as follow :

a) Before Insert Trigger

Syntax :

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;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2),
  create_date date,
  created_by varchar2(10)
);

CREATE OR REPLACE TRIGGER orders_before_insert
BEFORE INSERT
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing INSERT into table
   SELECT user INTO v_username
   FROM dual;
   
   -- Update create_date field to current system date
   :new.create_date := sysdate;
   
   -- Update created_by field to the username of the person performing the INSERT
   :new.created_by := v_username;
   
END;

When would i like to use and when not ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

B) After Insert Trigger

Syntax

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;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_insert
AFTER INSERT
   ON orders
   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;

When would i like to use and when not ?

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

C) Before Update Trigger

Syntax

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;

Example

CREATE TABLE orders
( 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)
);

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;

When would i like to use and when not ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

D) After Update Trigger

Syntax

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;

Example

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

CREATE OR REPLACE TRIGGER orders_after_update
AFTER UPDATE
   ON orders
   FOR EACH ROW
   
DECLARE
   v_username varchar2(10);
   
BEGIN

   -- Find username of person performing UPDATE into table
   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;

When would i like to use and when not ?

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

E) Before Delete Trigger

Syntax

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;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

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;

When would i like to use and when not ?

You can not create a BEFORE trigger on a view.

You can update the :NEW values.

You can not update the :OLD values.

F) After Delete Trigger

Syntax

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;

Example :

CREATE TABLE orders
( order_id number(5),
  quantity number(4),
  cost_per_item number(6,2),
  total_cost number(8,2)
);

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;

When would i like to use and when not :

You can not create an AFTER trigger on a view.

You can not update the :NEW values.

You can not update the :OLD values.

How to find current triggers in oracle :

Ans: Following data distcionary are present in oracle for viewing triggers information .

These are like tables so user can select from these tables to find information .

USER_TRIGGERS

ALL_TRIGGERS

DBA_TRIGGERS

Where do trigger stores ?

Ans: Triggers are stored in the data dictionary, within the SYSTEM tablespace. Their names and some properties are visible through views like dba_triggers (and all_triggers and user_triggers) and additionally through views dba_objects (and all_objects and user_objects) where object_type = 'TRIGGER'.

Is there any size limit on triggers ?
Ans : The size of the trigger cannot be more than 32K.
According to the Oracle Reference guide, the limit is imposed by the tool executing the stored procedure or trigger, and can be between 2000 and 3000 lines. It does not say how many characters or bytes this equates to, although non-stored SQL statements are limited to 64K characters. (But this statement is for oracle 9)

What is most efficient way of creating long triggers ?

Ans : The best way to write long triggers is to use them to call required procedures . They should be written to call procedures from packages which have procedures that do actual work

What points to be taken into consideration before planning to create trigger ?

Ans : guidelines when designing your triggers:

Use triggers to guarantee that when a specific operation is performed, related actions

are performed.
Do not define triggers that duplicate features already built into Oracle Database. For

example, do not define triggers to reject bad data if you can do the same checking

through declarative integrity constraints.
Limit the size of triggers. If the logic for your trigger requires much more than 60 lines of

PL/SQL code, it is better to include most of the code in a stored procedure and call the

procedure from the trigger.
Use triggers only for centralized, global operations that should be fired for the triggering

statement, regardless of which user or database application issues the statement.
Do not create recursive triggers. For example, creating an AFTER UPDATE statement

trigger on the Emp_tab table that itself issues an UPDATE statement on Emp_tab, causes the

trigger to fire recursively until it has run out of memory.
Use triggers on DATABASE judiciously. They are executed for every user every time the

event occurs on which the trigger is created.

Your Comments are invaluable for us . Please provide them. I like to welcome

questions that you need to find answer on this topic .....

Chears :)

Happy coding guys ..

What is a trigger ?

Post a Comment

Contact Form