dimanche 30 juillet 2017

If conditional in mysql trigger?

Straight forward, i have problem using conditional state in trigger mysql. There are 2 status, if the status is 'guest' then do something.. and else do another thing.

Here's what i already made :

CREATE TRIGGER `update-gain` AFTER INSERT ON `payment`
FOR EACH ROW BEGIN
declare v_profit integer;
declare v_deposit integer;
declare v_status CHAR;

select (status) into v_status from item where item.id_item=new.id_item;
select sum(profit_owner) into v_profit from payment where payment.id_item=new.id_item;
select sum(profit_guest ) into v_deposit from payment where payment.id_item=new.id_item;

if v_status like 'guest' then
INSERT INTO gain
(gain.profit, gain.modal,gain.date)
VALUES
(v_profit,v_deposit,new.date)
ON DUPLICATE KEY UPDATE
gain.profit = gain.profit+new.profit_owner,
gain.modal = gain.deposit+new.profit_guest;

else
INSERT INTO gain
(gain.profit,gain.tanggal)
VALUES
(v_profit,new.tgl_stok)
ON DUPLICATE KEY UPDATE
gain.profit = gain.profit_owner+new.profit_owner;

end if;
END

So far i'm using 'like' clause, but the condition always true, even v_status is not 'guest'.

What did i do wrong here? Thanks :)

Aucun commentaire:

Enregistrer un commentaire