使用 case 语句创建触发器

2022-01-09 00:00:00 sql insert oracle case triggers

我有这两张表:

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

users.username 是主键，commission_rates.username 是外键.

我想写一个触发器，插入用户后，检查是否role_id = 2，然后插入commission_rates users.username，佣金率为 0.

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;

任何帮助将不胜感激

推荐答案

应该是这样的:

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;

相关文章