Before row-level trigger v16

Suggest edits

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

← Prev

After statement-level trigger

After row-level trigger

Before row-level trigger v16

← Prev

Next →