Trigger Example

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;

 

 

Advertisements