Triggers

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

Instead of Trigger

Instead of Triggers:-
With the help of INSTEAD OF TRIGGER we perform DML on complex view.

Examples:-

CREATE OR REPLACE VIEW XXC05_EMP_DETAIL1
AS
(
SELECT E.EMPLOYEE_ID,
E.SALARY,
MAX(D.DEPARTMENT_ID)
FROM EMPLOYEES E,
DEPARTMENTS D,
LOCATIONS L
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND D.LOCATION_ID = L.LOCATION_ID
GROUP BY (E.EMPLOYEE_ID,SALARY)
);

create trigger XXC05_INSTEAD_OF_TRI
instead of update on XXC05_EMP_DETAIL1
begin
update employees
set employee_id = :new.employee_id
where employee_id = 198;
end XXC05_INSTEAD_OF_TRI;

UPDATE XXC05_EMP_DETAIL1
SET SALARY = SALARY+(SALARY*COMMISSION_PCT)
WHERE EMPLOYEE_ID = 198;

When we execute update command it update view data.