Introduction To PLSQL Triggers and Types of Triggers

/*****************************************************************************
         Oracle PLSQL TRIGGERS..
TYPES OF TRIGGERS
-----------------
1) DML Triggers
2) DDL Triggers
3) SYSTEM Triggers (STARTUP, SHUTDOWN) and user Evens (LOGON & LOGOFF)
4) INSTEAD OF Trigger

TYPES OF DML Triggers:
----------------------
ROW LEVEL & STATEMENT  LEVEL TRIGGERS
=====================================
Firing Point : BEFORE
=========================
  1)  BEFORE INSERT TRIGGER
  2)  BEFORE UPDATE TRIGGER
  3)  BEFORE DELETE TRIGGER

Firing Point : AFTER
=====================
  1)  AFTER INSERT TRIGGER
  2)  AFTER UPDATE TRIGGER
  3)  AFTER DELETE TRIGGER
 

OLD & NEW Qualifiers.
-----------------------
INSERT :  NEW
UPDATE :  OLD & NEW
DELETE :  OLD

Conditional Predicates
--------------------------
INSERTING
UPDATING
DELETING
*******************************************************************************/
CREATE [OR REPLACE] TRIGGER trigger_name       
    BEFORE | AFTER
    [INSERT, UPDATE, DELETE [COLUMN NAME..]
    ON table_name

    Referencing [ OLD AS OLD | NEW AS NEW ]
    FOR EACH ROW | FOR EACH STATEMENT [ WHEN Condition ]

DECLARE
    [declaration_section
        variable declarations;
        constant declarations;
    ]

BEGIN
    [executable_section
        PL/SQL execute/subprogram body
    ]

EXCEPTION
    [exception_section
        PL/SQL Exception block
    ]

END;
********************************************************************************
/


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



CREATE or REPLACE TRIGGER EMP_DELETE_TRIGGER
    AFTER
    DELETE ON emp
    FOR EACH ROW
BEGIN
    IF :old.empno = 7782 THEN
  null;
        raise_application_error(-20015, 'You cant delete this row');
    END IF;
END;


delete from emp where empno=7782;

select * from mytable;
select * from emp;


CREATE or REPLACE TRIGGER EMP_DELETE_TRG_1
    BEFORE
    DELETE ON emp
    FOR EACH ROW
BEGIN
   
        insert into mytable values(:old.empno,sysdate);
   
END;