Before row-level trigger v17
This example shows a before row-level trigger that calculates the commission of every new employee belonging to department 30 that's inserted into the emp
table:
CREATE OR REPLACE TRIGGER emp_comm_trig BEFORE INSERT ON emp FOR EACH ROW BEGIN IF :NEW.deptno = 30 THEN :NEW.comm := :NEW.sal * .4; END IF; END;
The listing following the addition of the two employees shows that the trigger computed their commissions and inserted it as part of the new employee rows:
INSERT INTO emp VALUES (9005,'ROBERS','SALESMAN',7782,SYSDATE,3000.00,NULL,30); INSERT INTO emp VALUES (9006,'ALLEN','SALESMAN',7782,SYSDATE,4500.00,NULL,30); SELECT * FROM emp WHERE empno IN (9005, 9006);
Output
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO ------ ------ -------- ------ ---------- --------- ---------- ------------- 9005 ROBERS SALESMAN 7782 01-APR-05 3000 1200 30 9006 ALLEN SALESMAN 7782 01-APR-05 4500 1800 30