The following example is a simple before statement-level trigger that displays a message prior to an insert operation on the emp table.
CREATE OR REPLACE TRIGGER emp_alert_trig BEFORE INSERT ON emp BEGIN DBMS_OUTPUT.PUT_LINE('New employees are about to be added'); END;
The following INSERT is constructed so that new rows are inserted upon a single execution of the command. For each row that has an employee ID between 7900 and 7999, a new row is inserted with an employee ID incremented by 1000. The following example shows the results of executing the command with three new rows inserted.
INSERT INTO emp (empno, ename, deptno) SELECT empno + 1000, ename, 40 FROM emp WHERE empno BETWEEN 7900 AND 7999; New employees are about to be added SELECT empno, ename, deptno FROM emp WHERE empno BETWEEN 8900 AND 8999; EMPNO ENAME DEPTNO ---------- ---------- ---------- 8900 JAMES 40 8902 FORD 40 8934 MILLER 40
The message "New employees are about to be added" is displayed once after the trigger is fired even though the result is that three new rows have been added.