Create a trigger after insert with a condition

Question

I want to create a trigger to insert into another table the ID row if a certain conditions are verified.

SWAB_TEST

id_swab
id_user 
result

NO_INFECTED

id_user

For example i want to insert into NO_INFECTED table the ID of the user who swab test is negative

SWAB_TEST

id_swab : Test1
id_user : 1
result  : negative

NO_INFECTED

id_user: 1

I created this trigger but I don't know how to insert the condition

CREATE OR REPLACE TRIGGER test_trigg 
AFTER INSERT ON swab_test
FOR EACH ROW
BEGIN
 INSERT INTO no_infected (id_user)
 VALUES(:new.id_user);
END;

Just to comment on coding style, you don't have to code in block caps (although, sadly, a lot of people do). Also, a 1 character indent seems to me pessimistic :) — William Robertson

Barbaros Özhan Barbaros Özhan · Accepted Answer · 2020-05-05T18:03:42

Just need to add an IF condition with :new.result = 'negative' control :

CREATE OR REPLACE TRIGGER test_trigg 
AFTER INSERT ON swab_test
FOR EACH ROW
BEGIN
IF :new.result = 'negative' THEN
 INSERT INTO no_infected (id_user)
 VALUES (:new.id_user);
END IF;
END;
/

Create a trigger after insert with a condition

1 Answers