If Else Condition at Trigger in Oracle SQL

Question

I have two tables.They are

CARD(cardid, credit, usertype,charge)

and

PAYMENTDEVICE(paydevid, paydevip,paydevdate, paydevtime, chargedcardid, mealtype).

Mealtype can be 'guest' or 'standard'. I want to update credit that is in card table, when a new row is inserted in paymentdevice. Charge depends on usertype. But if meal type is guest, everyone has to pay 5$ . I try to use following code

CREATE OR REPLACE TRIGGER  "TRG_PAYMONEY" 
AFTER INSERT
ON PAYMENTDEVICE FOR EACH ROW

BEGIN
UPDATE CARD
WHERE CARDID = :NEW.CHARGEDCARDID
SET CREDIT = 
(CASE MEALTYPE

WHEN "STANDARD" THEN CREDIT - CHARGE
WHEN "GUEST" THEN CREDIT - 5
END);
END;

But i get this error : PL/SQL: ORA-00971: missing SET keyword, PL/SQL: SQL Statement ignored. Could you help me please?

Mariappan Subramanian · Accepted Answer · 2012-12-25 16:31:26Z

5

Follow this syntax for update,

update table_name set field1='value' where field2='value'

(ie)

UPDATE CARD
SET CREDIT = 
(CASE MEALTYPE
WHEN "STANDARD" THEN CREDIT - CHARGE
WHEN "GUEST" THEN CREDIT - 5
END)
 WHERE CARDID = :NEW.CHARGEDCARDID;

For more, how 'update' statement works refer to this doc,

http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm

For your second error try this,

UPDATE CARD
SET CREDIT = 
(CASE 
WHEN MEALTYPE="STANDARD" THEN CREDIT - CHARGE
WHEN MEALTYPE="GUEST" THEN CREDIT - 5
END MEALTYPE) 
 WHERE CARDID = :NEW.CHARGEDCARDID;

Something like this,

update card c
set c.credit=(select case when p.mealtype='STANDARD' then c.credit-c.charge
                 when p.mealtype='GUEST' then c.credit-5
            end credit from PAYMENTDEVICE p
           where c.cardid=p.chargedcardid)

fiddle_demo

edited Dec 25, 2012 at 16:31

answered Dec 25, 2012 at 14:47

Mariappan Subramanian

10.2k9 gold badges34 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

rs. · Accepted Answer · 2012-12-25 14:47:29Z

1

Your update statement should be (WHERE should be after SET)

UPDATE CARD
SET CREDIT = 
(CASE MEALTYPE

WHEN "STANDARD" THEN CREDIT - CHARGE
WHEN "GUEST" THEN CREDIT - 5
WHERE CARDID = :NEW.CHARGEDCARDID

answered Dec 25, 2012 at 14:47

rs.

27.6k13 gold badges71 silver badges93 bronze badges

Comments

Dileep · Accepted Answer · 2012-12-26 13:10:54Z

0

please run this code..

CREATE OR REPLACE TRIGGER TRG_PAYMONEY1
AFTER INSERT ON PAYMENTDEVICE 
FOR EACH ROW
BEGIN
UPDATE CARD c
SET c.credit=(select case when p.mealtype='STANDARD' then c.credit-c.charge
                 when p.mealtype='GUEST' then c.credit-5
            end result from PAYMENTDEVICE p,card c
           where c.cardid=p.chargedcardid)
WHERE CARDID = :NEW.CHARGEDCARDID;
END;

answered Dec 26, 2012 at 13:10

Dileep

6743 gold badges10 silver badges20 bronze badges

1 Comment

kparkin Over a year ago

It still gives that error ORA-04091: table SYSTEM.PAYMENTDEVICE is mutating, trigger/function may not see it ORA-06512: at "SYSTEM.TRG_PAYMONEY1", line 2 ORA-04088: error during execution of trigger 'SYSTEM.TRG_PAYMONEY1'

kparkin · Accepted Answer · 2012-12-26 21:11:04Z

0

Well, after a little research and thanks to Oracle Documentation we solved our problem with this implementation :

CREATE OR REPLACE TRIGGER  "TRG_PAYMONEY" 
AFTER INSERT
ON PAYMENTDEVICE FOR EACH ROW

BEGIN
IF :NEW.MEALTYPE='GUEST' THEN
UPDATE CARD
SET CREDIT = CREDIT - 5
WHERE CARDID = :NEW.CHARGEDCARDID;
ELSE
UPDATE CARD
SET CREDIT = CREDIT - CHARGE
WHERE CARDID = :NEW.CHARGEDCARDID;
END IF;
END;

And thank you for your help & advices.

answered Dec 26, 2012 at 21:11

kparkin

3453 gold badges7 silver badges20 bronze badges

Collectives™ on Stack Overflow

If Else Condition at Trigger in Oracle SQL

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

Comments

Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related