The forum will experience an outage sometime between February 10 at 7:00pm EST and February 12 at 11:59 EST for installation of security updates. The actual time and duration of the outage are unknown but attempts will be made to minimize the downtime. We apologize for any inconvenience.
无法在数据库中修改触发器 '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

asked 13 Nov '13, 05:06

mfkpie8's gravatar image

mfkpie8
86404651
accept rate: 10%

edited 13 Nov '13, 05:19

1

What is your question?

(13 Nov '13, 05:10) Justin Willey
Replies hidden

I have revised my question

(13 Nov '13, 05:20) mfkpie8

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

(13 Nov '13, 05:28) Justin Willey

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]

ERROR

permanent link

answered 13 Nov '13, 08:29

mfkpie8's gravatar image

mfkpie8
86404651
accept rate: 10%

edited 13 Nov '13, 08:31

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
permanent link

answered 13 Nov '13, 11:52

mfkpie8's gravatar image

mfkpie8
86404651
accept rate: 10%

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
permanent link

answered 13 Nov '13, 09:53

mfkpie8's gravatar image

mfkpie8
86404651
accept rate: 10%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×58

question asked: 13 Nov '13, 05:06

question was seen: 718 times

last updated: 13 Nov '13, 11:52