如果在 Oracle SQL 中触发的 Else 条件
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/14031593/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
If Else Condition at Trigger in Oracle SQL
提问by kparkin
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
膳食类型可以是“客人”或“标准”。当在支付设备中插入新行时，我想更新卡表中的信用。收费取决于用户类型。但是如果用餐类型是客人，每个人都必须支付5美元。我尝试使用以下代码
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?
但我收到此错误：
PL/SQL: ORA-00971: missing SET keyword, PL/SQL: SQL Statement ignored。请问你能帮帮我吗？
回答by Mari
Follow this syntax for update,
按照此语法进行更新，
update table_name set field1='value' where field2='value'
(ie)
（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,
有关更多信息，'update'请参阅此文档，
http://docs.oracle.com/cd/B19306_01/appdev.102/b14261/update_statement.htm
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)
回答by rs.
Your update statement should be (WHEREshould be after SET)
你的更新语句应该是（WHERE应该在之后SET）
UPDATE CARD
SET CREDIT =
(CASE MEALTYPE
WHEN "STANDARD" THEN CREDIT - CHARGE
WHEN "GUEST" THEN CREDIT - 5
WHERE CARDID = :NEW.CHARGEDCARDID
回答by Dileep
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;
回答by kparkin
Well, after a little research and thanks to Oracle Documentationwe solved our problem with this implementation :
好吧，经过一些研究并感谢 Oracle 文档，我们通过这个实现解决了我们的问题：
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.
并感谢您的帮助和建议。
