The following example is a BEFORE row-level trigger that calculates the commission of every new employee belonging to department 30 that is 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 list 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);
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