Creating a trigger with a case statement

问题:

I have these two tables:

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

users.username is the primary key, commission_rates.username is a foreign key.

I want to write a trigger, after an insert on users, check if role_id = 2, then insert into commission_rates the users.username, and 0 for commission rate.

This is what I have so far, it doesn't work though:

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

回答1:

It should go 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;

回答2:

WHEN condition must be in the definition part, not in the body. They can only by used for row trigger.

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;