Press "Enter" to skip to content

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;

Output

EMPNO

ENAME

JOB

MGR

HIREDATE

SAL

COMM

DEPTNO

7499

ALLEN

CLERK

7698

20/02/1981

1600

0

30

See also:

Vinish Kapoor

Hi, I am a full stack developer and writing about development. I document everything I learn and help thousands of people. foxinfotech.in is created, written, and maintained by me; it is built on WordPress, and hosted by Bluehost. Connect with me on Facebook, Twitter, GitHub, and get notifications for new posts.

Be First to Comment

    Leave a Reply

    Your email address will not be published. Required fields are marked *