无法在数据库中修改触发器 'tr_shipper_up - shipper (DBA)'。 第 在第 6 行 行的 'then' 附近有语法错误 [Sybase][ODBC Driver][SQL Anywhere]第 在第 6 行 行的 'then' 附近有语法错误 SQLCODE: -131 SQLSTATE: 42000 SQL Statement: ALTER TRIGGER "tr_shipper_up" after update order 1 on dba.shipper referencing new as new_shipper for each row begin if(( (select if(shipper.approved=new_shipper.approved then 1 else 0 endif) from new_shipper,shipper where new_shipper.trans_no=shipper.trans) )--更新的是否有变化 then update customer set customer.user_7=customer.user_7+(shipper_line.quantity*part.user_10) from shipper,shipper_line,customer,part where shipper_line.trans_no = new_shipper_line.trans_no and shipper_line.line_no = new_shipper_line.line_no and shipper_line.trans_no=shipper.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' ; else update shipper_line set create_time = getdate(*) where shipper_line.trans_no = new_shipper_line.trans_no and shipper_line.line_no = new_shipper_line.line_no end if; end |
ALTER TRIGGER "tr_shipper_up" after update order 1 on dba.shipper referencing new as new_shipper for each row begin case when (select count(*) from shipper s inner join new_shipper on new_shipper.trans_no=shipper.trans and new_shipper.approved<>s.approved and new_shipper.approved='Y')>0 --是否被更新 then update customer set customer.user_7=customer.user_7-(shipper_line.quantity*part.user_10) from shipper,shipper_line,customer,part,new_shipper where new_shipper.trans_no=shipper.trans_no and shipper_line.trans_no=shipper.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' ; when (select count(*) from shipper s inner join new_shipper s on new_shipper.trans_no=shipper.trans and new_shipper.approved<>s.approved and new_shipper.approved='N')>0 then update customer set customer.user_7=customer.user_7+(shipper_line.quantity*part.user_10) from shipper,shipper_line,customer,part,new_shipper where new_shipper.trans_no=shipper.trans_no and shipper_line.trans_no=shipper.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' ; else update shipper set edit_time = getdate(*) where shipper.trans_no = new_shipper.trans_no end case; end **ERROR : can not find table "new_shipper"** ![link text][1]
You are writing a ROW-LEVEL trigger, as such new_shipper refers to a single row, not a table, and therefore it cannot be used within a FROM clause - in contrast, you can use new_shipper.trans_no (and the like) simply like a variable. If you want to use new_shipper it as a table, you should change your trigger to a statement-level trigger by replacing FOR EACH ROW with FOR EACH STATEMENT.
(13 Nov '13, 09:08)
Volker Barth
|
|
thank for all; ALTER TRIGGER "T" AFTER UPDATE OF "approved" ORDER 3 ON "DBA"."shipper" referencing new as new_shipper3 FOR EACH ROW /* WHEN( 搜索条件 ) */ BEGIN case when new_shipper3.approved='Y' --if update then update DBA.customer set customer.user_7=customer.user_7-(shipper_line.quantity*isnull(part.user_10,0)) from DBA.shipper,DBA.shipper_line,DBA.customer,DBA.part where new_shipper3.trans_no=shipper.trans_no and shipper.trans_no=shipper_line.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' when new_shipper3.approved='N' then update Dba.customer set customer.user_7=customer.user_7+(shipper_line.quantity*isnull(part.user_10,0)) from dba.shipper,dba.shipper_line,dba.customer,dba.part where new_shipper3.trans_no=shipper.trans_no and shipper.trans_no=shipper_line.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' else update shipper set edit_time = getdate(*) where shipper.trans_no = new_shipper3.trans_no end case end |
The reason for the error is: the table name needs to be added in front of the DBA ", why we need to add the DBA I have found the answer and has been fixed, but when the trigger is not implemented, "approved = 'y' or is = 'n'" right answer: ALTER TRIGGER "tr_shipper_up" after update order 1 on dba.shipper referencing new as new_shipper for each row begin case when (select count(*) from dba.shipper where new_shipper.trans_no=shipper.trans_no and new_shipper.approved<>shipper.approved and new_shipper.approved='Y') >0 --check vaule approved is update and vaule='y' then update DBA.customer set customer.user_7=customer.user_7-sum(shipper_line.quantity*isnull(part.user_10,0)) from DBA.shipper,DBA.shipper_line,DBA.customer,DBA.part where new_shipper.trans_no=shipper.trans_no and shipper.trans_no=shipper_line.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' when (select count(*) from dba.shipper where new_shipper.trans_no=shipper.trans_no and new_shipper.approved<>shipper.approved and new_shipper.approved='N') >0 check vaule approved is update and vaule='N' then update Dba.customer set customer.user_7=customer.user_7+sum(shipper_line.quantity*isnull(part.user_10,0)) from dba.shipper,dba.shipper_line,dba.customer,dba.part where new_shipper.trans_no=shipper.trans_no and shipper.trans_no=shipper_line.trans_no and shipper.customer_id=customer.customer_id and shipper_line.part_id=part.part_id and part.product_code ='XQ' else update shipper set edit_time = getdate(*) where shipper.trans_no = new_shipper.trans_no end case end |
What is your question?
I have revised my question
Thanks. As your error message says - you have an error in line 6. You need to check the syntax of your IF expression. See http://dcx.sybase.com/index.html#sa160/zh/dbreference/if-expressions-wsqlref.html