Before Row-Level Trigger v13

Suggest edits

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 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);

 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 v13

← Prev

Next →