CODEWITHSUNDEEP
sqloracle-databasetriggersinsertcase
user2706577
user2706577

Reputation: 41

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

Upvotes: 4

Views: 8427

Answers (2)

Meldor
Meldor

Reputation: 236

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;

Upvotes: 2

Philippe Banwarth
Philippe Banwarth

Reputation: 17725

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;

Upvotes: 1

Related Questions