CODEWITHSUNDEEP
sqloracle-databaseplsql
user2488578
user2488578

Reputation: 916

PL/SQL : Apply condition only for INSERTING and not for UPDATING

My requirement is to insert into a table only when COLUMN1 IS NULL. I have the below code. But, the problem here is, the update will not happen if COLUMN1 is NOT NULL. I want the update to happen even if COLUMN1 IS NULL.

How to apply the NULL condition check only for insert and not for update?

CREATE OR REPLACE TRIGGER MYTRIGGER 
AFTER INSERT OR UPDATE OR DELETE ON MYTABLE1
FOR EACH ROW
when (NEW.COLUMN1 IS NULL)
BEGIN
IF INSERTING THEN
INSERT INTO MYTABLE2(COLUMN2) VALUES (:NEW.COLUMN1);
 ELSIF UPDATING THEN
UPDATE MYTABLE2 SET COLUMNX = :NEW.COLUMNX;
END IF;
END MYTRIGGER;

Upvotes: 0

Views: 62

Answers (1)

Ankur Patel
Ankur Patel

Reputation: 1433

Try the following:

CREATE OR REPLACE TRIGGER MYTRIGGER 
AFTER INSERT OR UPDATE OR DELETE ON MYTABLE1
FOR EACH ROW
BEGIN
IF INSERTING AND (:NEW.COLUMN1 IS NULL) THEN
INSERT INTO MYTABLE2(COLUMN2) VALUES (:NEW.COLUMN1);
 ELSIF UPDATING THEN
UPDATE MYTABLE2 SET COLUMNX = :NEW.COLUMNX;
END IF;
END MYTRIGGER;

Or you can create 2 different triggers for insert and update.

Upvotes: 1

Related Questions