Oracle Trigger WHEN Clause Example

Written by
Vinish Kapoor is a seasoned software development professional and a fervent enthusiast of artificial intelligence (AI). His impressive career spans over 20 years, marked by a relentless pursuit of innovation and excellence in the field of information technology. As an Oracle ACE, Vinish has distinguished himself as a leading expert in Oracle technologies, a title awarded to individuals who have demonstrated their deep commitment, leadership, and expertise in the Oracle community.
  • PLSQL
  • 2 mins read

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: