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.