Oracle Trigger WHEN Clause Example

In this article, you will learn how to use WHEN clause in Oracle trigger to make the trigger fire on the specified condition. Below is an example.

You can test this trigger example by creating the following table and trigger in your Oracle database schema.

Create Table

CREATE TABLE EMP
(
EMPNO NUMBER(4) NOT NULL,
ENAME VARCHAR2(10 BYTE),
JOB VARCHAR2(9 BYTE),
MGR NUMBER(4),
HIREDATE DATE,
SAL NUMBER(7,2),
COMM NUMBER(7,2),
DEPTNO NUMBER(2)
)
/

Create an Oracle Database Trigger With WHEN Clause

The following trigger will fire only when the JOB is equal to CLERK, and if it is, then it will set the COMM column (commission) to 0.

CREATE OR REPLACE TRIGGER emp_trig_1
BEFORE INSERT
ON EMP
FOR EACH ROW
WHEN (NEW.job = 'CLERK')
BEGIN
:NEW.comm := 0;
END;
/

Test

The below insert statement for EMP table will try to insert a record for CLERK with commission 300.

SET DEFINE OFF;
Insert into EMP
(EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
Values
(7499, 'ALLEN', 'CLERK', 7698,
TO_DATE('02/20/1981 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 1600, 300, 30);
COMMIT;

Query The Table

Now query the EMP table for the above-inserted record, and you will find that the COMM column value is 0.

SELECT *
FROM EMP
WHERE EMPNO = 7499;

See also:

• Learn How to Create a Trigger in Oracle SQL Developer
• Create Triggers in Oracle Forms Example