Triggers and its examples....

Trigger:-    Trigger is a pl/sql block or procedure that implicitly execute when some event occur.

Application Trigger:- Fire whenever an event occurs with a particular application.
Database Trigger:- Fire whenever a data event(such as DML) or system event(such as logon or
                                 shutdown) occur on a schema or database

Trigger Timing
    For table:- Before, After
    For View:- Instead of
   
Trigger Event:-   
    Insert, Update or Delete

Trigger Name:-
    On Table, View
   
Trigger Type:-
        Row Level
        Statement Level
       
Trigger Body:-
            What Action perform
           

CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT ON XXC05_TRIGGER_TEST
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
    END IF;
END XXC05_TRIGGER_TEST;   




CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        IF INSERTING
        THEN
            RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
        ELSIF UPDATING
        THEN
            RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');   
        ELSIF DELETING
        THEN
              RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE'); 
        END IF;       
    END IF;
END XXC05_TRIGGER_TEST;


CREATE OR REPLACE TRIGGER XXC05_TRIGGER001
BEFORE INSERT OR UPDATE OR DELETE ON XXC05_TRIGGER_TEST
FOR EACH ROW
BEGIN
    IF TO_CHAR(SYSDATE,'DY') = 'SAT'
    THEN
        IF INSERTING
        THEN
            RAISE_APPLICATION_ERROR(-20500,'YOU NOT INSERT DATA INTO XXC05_TRIGGER_TEST TABLE');
        ELSIF UPDATING
        THEN
            RAISE_APPLICATION_ERROR(-20501,'YOU NOT UPDATE DATA INTO XXC05_TRIGGER_TEST TABLE');   
        ELSIF DELETING
        THEN
              RAISE_APPLICATION_ERROR(-20502,'YOU NOT DELETE DATA INTO XXC05_TRIGGER_TEST TABLE'); 
        END IF;       
    END IF;
END XXC05_TRIGGER_TEST;

Filed Under: PL SQL Thursday 27 February 2014