Home > database >  can anyone help me with the trigger insert problem i created?
can anyone help me with the trigger insert problem i created?

Time:12-11

so I made a trigger insert like this:

create or replace trigger discount
after insert on transaction
for each row
begin
if (new.desc_date = 'Y') then
insert into desc_transaction(discount) values (new.discount = '0.1');
end if;
end;
/

Warning: Trigger created with compilation errors.

show error;
3/1      PL/SQL: SQL Statement ignored
3/54     PL/SQL: ORA-00917: missing comma

how to solve it.

data : desc_date contains only yes and no because if November is discounted and October is not. For the discount section contains 0.1 and null so if desc_date is yes then 0.1 otherwise it will be null or ' '.

CodePudding user response:

Wrong syntax. Should've been

CREATE OR REPLACE TRIGGER discount
   AFTER INSERT
   ON transaction
   FOR EACH ROW
BEGIN
   IF :new.desc_date = 'Y'
   THEN
      INSERT INTO desc_transaction (discount)
           VALUES (0.1);
   END IF;
END;
/

Or, possibly even better,

CREATE OR REPLACE TRIGGER discount
   AFTER INSERT
   ON transaction
   FOR EACH ROW
BEGIN
   INSERT INTO desc_transaction (discount)
        VALUES (CASE WHEN :new.desc_date = 'Y' THEN 0.1 ELSE NULL END);
END;
/

CodePudding user response:

The syntax of your insert statement is incorrect. Try the following:

insert into desc_transaction(discount) values ('0.1');

Side note - discount probably isn't a varchar column. If this guess is correct, you should be using a numeral literal (0.1) and not a string literal ('0.1') like you currently have.

  • Related