Trigger (Last Updated on 23-Oct-2014)
What is a trigger ?
ans : 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.
System 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.- 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 ?
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 ?
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 ?
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 ?
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 ?
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 ?
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.
- 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 theEmp_tab
table that itself issues anUPDATE
statement onEmp_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.
UR Comments are invaluable for us . Please provide them . I like to welcome questions that U need to find answer on this topic ..... next update coming soon