INTRODUCTION TO PL/SQL 6:

PLSQL Triggers(DML):

TRIGGERS (DML Manipulations:):

A TRIGGER is a PL/SQL block that executes implicitly whenever a particular event takes place.
The specified event is associated with either a table, a view, a schema, or the database, and it is one of the following:

When creating triggers we need to focous mainly on the Trigger Timing, Trigger Event, Trigger Type.

TRIGGER TIMING:

FOR TABLE : BEFORE,AFTER

FOR VIEW : INSTEAD OF.

TRIGGERING EVENT: INSERT, UPDATE OR DELETE.

TABLE NAME: ON TABLE OR VIEW.

TRIGGER TYPE : ROW OR STATEMENT.

WHEN CLAUSE : RESTRICTING CONDITION

TRIGGER BODY : PL/SQL BLOCK.

TRIGGER COMPONENTS:

TRIGGER TIMING: when a should the trigger fire?

BEFORE: EXECUTE the TRIGGER body BEFORE the triggering dml event on a table.

AFTER: EXECUTE the TRIGGER body AFTER the triggering dml event on a table.

INSTEAD OF: EXECUTE the TRIGGER body INSTEAD of the triggering statement. Used for views that are not otherwise modifiable.

TRIGGER TYPE:

STATEMENT: The TRIGGER body executes once for the triggering event. this is the default.

ROW: The TRIGGER body executes once FOR EACH ROW affected by the triggering event.

What is the action that should trigger perform?

The TRIGGER BODY is a PL/SQL BLOCK or a call to a procedure.
When the triggering data manipulation statement affects many rows, the statement trigger fires exactly once, and the row trigger fires once for every row affected by statement.

CREATING STATEMENT TRIGGERS:

CREATE OR replace TRIGGER business_day_emp
BEFORE INSERT ON emp
BEGIN
IF ( To_char(SYSDATE, 'DY') IN ( 'SAT', 'SUN' ) )
OR ( To_char(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' ) THEN
Raise_application_error (-20500, 'YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS');
END IF;
END;
/
Trigger created.
INSERT INTO EMP during non office hours.
INSERT INTO emp
(empno,
ename,
deptno)
VALUES (1,
'RAJESH',
40);
INSERT INTO EMP
*
ERROR at line 1:
ORA-20500: YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS
ORA-06512: at "SCOTT.BUSINESS_DAY_EMP", line 4
ORA-04088: error during execution of trigger 'SCOTT.BUSINESS_DAY_EMP'
INSERT INTO EMP DURING OFFICE HOURS.
INSERT INTO emp
(empno,
ename,
deptno)
VALUES (1,
'RAJESH',
40);
1 row created.

USING CONDITIONAL PREDICATES:

CREATE OR replace TRIGGER business_day_emp1
BEFORE INSERT OR UPDATE OR DELETE ON emp
BEGIN
IF ( To_char(SYSDATE, 'DY') IN ( 'SAT', 'SUN' ) )
OR ( To_char(SYSDATE, 'HH24') NOT BETWEEN '08' AND '18' ) THEN
IF deleting THEN
Raise_application_error (-20502,
'YOU MAY ONLY DELETE FROM EMP DURING BUSINESS HOURS');
ELSIF inserting THEN
Raise_application_error (-20500,
'YOU MAY ONLY INSERT INTO EMP DURING BUSINESS HOURS'
);
ELSIF Updating ('SAL') THEN
Raise_application_error (-20503,
'YOU MAY ONLY UPDATE SAL IN EMP DURING BUSINESS HOURS'
);
ELSE
Raise_application_error (-20504,
'YOU MAY ONLY UPDATE EMP DURING BUSINESS HOURS');
END IF;
END IF;
END;
/
Trigger created.