Creating a trigger with case argument

Question

I have these two tables:

USERS(username, role_id) COMMISSION_RATES(username, commission_rate) 

users.username is the main key, commission_rates.username is the foreign key.

I want to write a trigger after pasting on a user, check if there is role_id = 2 , then paste in commission_rates users.username and 0 for commission.

This is what I still have, this does not work:

 create or replace TRIGGER SETCOMISSIONRATE AFTER INSERT ON USERS BEGIN CASE WHEN users.role_id = 2 THEN INSERT INTO COMISSION_RATE (username, comission_rate) VALUES ( :NEW.username, 0) END; 

Any help would be appreciated

Answer 1

It should look like this:

 CREATE OR REPLACE TRIGGER SETCOMISSIONRATE AFTER INSERT ON USERS FOR EACH ROW WHEN (new.role_id = 2) BEGIN INSERT INTO COMISSION_RATE (username, comission_rate) VALUES (:new.username, 0); END; 

Answer 2

WHEN the condition should be in the definition part, and not in the body. They can only be used to trigger a string.

 create or replace TRIGGER SETCOMISSIONRATE AFTER INSERT ON USERS FOR EACH ROW WHEN (NEW.role_id = 2) BEGIN INSERT INTO COMISSION_RATE (username, comission_rate) VALUES ( :NEW.username, 0) END;