PLSQL Trigger

Trigger

Triggers :-Trigger associated with DML statement, when DML statement execute trigger implicitly executed.

Component of Trigger:-
Triggering SQL statement:- SQL DML statement (INSERT, UPDATE and DELETE) that execute and implicitly called trigger to execute.

Trigger Action:- When the triggering SQL statement is execute, trigger automatically call and PL/SQL trigger block execute.

Trigger Restriction:- We can specify the condition inside trigger to when trigger need fire.

Type of Triggers:-
    1) BEFORE Trigger:- BEFORE trigger execute before the triggering DML statement (INSERT, UPDATE, DELETE) execute. Triggering SQL statement is may or may not execute, depending on the BEFORE trigger conditions block.
    

2) AFTER Trigger:- AFTER trigger execute after the triggering DML statement (INSERT, UPDATE, DELETE) executed. Triggering SQL statement is execute as soon as followed by the code of trigger before performing Database operation.
    

3) ROW Trigger:- ROW trigger fire for each and every record which are performing INSERT, UPDATE, DELETE from the database table. If row deleting is define as trigger event, when trigger file, deletes the five rows each times from the table.
    

4) Statement Trigger:- Statement trigger fire only once for each statement. If row deleting is define as trigger event, when trigger file, deletes the five rows at once from the table.
    

5) Combination Trigger:- Combination trigger are combination of two trigger type,
        1) Before Statement Trigger:- Trigger fire only once for each statement before the triggering DML statement.
        2) Before Row Trigger:- Trigger fire for each and every record before the triggering DML statement.
        3) After Statement Trigger:- Trigger fire only once for each statement after the triggering DML statement executing.
        4) After Row Trigger:- Trigger fire for each and every record after the triggering DML statement executing.

Syntax:-We can create trigger through create trigger statement

CREATE [OR REPLACE] TRIGGER trigger_name       
    BEFORE | AFTER   INSERT or UPDATE or DELETE  [of COLUMN NAME,..]
    ON table_name
    Referencing [ OLD AS OLD | NEW AS NEW ]
    FOR EACH ROW | FOR EACH STATEMENT 
[ WHEN Condition ]
DECLARE
      variable declarations;
      constant declarations;
BEGIN
    Executable statement;
EXCEPTION
    Exception statement;
END;

Example:- Table for implementing trigger
drop table orders;
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),
  updated_date date,
  updated_by varchar2(10)
);

Before insert Trigger

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

insert into orders (order_id,quantity,cost_per_item,total_cost)  values(111,2,12,24);
commit;

select * from orders;

After insert trigger

drop table orders_audit;
create table orders_audit
( 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_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,create_date,created_by )
VALUES( :new.order_id,:new.quantity,:new.cost_per_item,:new.total_cost,sysdate,v_username );
END;
/

insert into orders (order_id,quantity,cost_per_item,total_cost)  values(112,4,11,44);
commit;

select * from orders;
select * from orders_audit;

Before update trigger

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

update orders set order_id=113 where order_id=112;
commit;
select * from orders;

After update trigger

drop table update_orders_audit;
create table update_orders_audit
( order_id number(5),quantity_before number(4),
quantity_after number(4),username varchar2(20));

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 update_orders_audit( order_id,quantity_before,quantity_after,username )
VALUES( :new.order_id,:old.quantity,:new.quantity,v_username );
END;
/

update orders set quantity=5 where order_id=113;
commit;
select * from update_orders_audit;

Before Delete Trigger

drop table delete_orders_audit;
create table delete_orders_audit
( order_id number(5),quantity number(4),
cost_per_item number(5),total_cost number(5),delete_date date,deleted_by varchar2(20)
);

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

delete from orders where order_id=113;
commit;
select * from orders where order_id=113;
select * from delete_orders_audit;

After delete trigger

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

Enable/Disable triggers

alter trigger {disable|enable};

alter trigger orders_after_delete disable;
alter trigger orders_after_delete enable;

alter table orders enable all triggers;
alter table orders disable all triggers;

Drop a Trigger
drop trigger ;
drop trigger orders_after_delete;


Aborting Triggers with Error

Triggers can often be used to enforce constraints.
The WHEN clause or body of the trigger can check for the violation of certain
conditions and signal an error accordingly using the Oracle built-in function
RAISE_APPLICATION_ERROR. The action that activated the trigger
(insert, update, or delete) would be aborted.

Example:- The following trigger enforces the constraint Person.age >= 0

create table Person (age int);

CREATE TRIGGER PersonCheckAge
AFTER INSERT OR UPDATE OF age ON Person
FOR EACH ROW
BEGIN
    IF (:new.age < 0) THEN
        RAISE_APPLICATION_ERROR(-20000, 'no negative age allowed');
    END IF;
END;
/

insert into Person values (-3);
commit;

Before Insert Trigger

This trigger execute BEFORE to convert ename field lowercase to uppercase.

CREATE or REPLACE TRIGGER trg1
    BEFORE INSERT ON emp1 FOR EACH ROW
BEGIN
    :new.ename := upper(:new.ename);
END;
/

Restriction to Delete records Trigger

This trigger is preventing to deleting row.

CREATE or REPLACE TRIGGER trg1
    AFTER DELETE ON emp1 FOR EACH ROW
BEGIN
    IF :old.eno = 1 THEN
        raise_application_error(-20015, 'You can't delete this row');
    END IF;
END;
/

delete from emp1 where eno = 1;

Data Dictionaries View  for Trigger:-
SQL>select * from user_triggers;
SQL>select * from USER_TRIGGER_COLS;
SQL>select * from user_objects where object_type='TRIGGER';

How we can get the trigger code ?
SQL>select text from user_source
where type='TRIGGER' and name='ORDERS_BEFORE_UPDATE';

SQL>select dbms_metadata.get_ddl('TRIGGER','ORDERS_BEFORE_UPDATE') from dual;