ERROR trigger

[ximen]

无法在数据库中修改触发器 '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

[justin_willey]

What is your question?

[ximen]

I have revised my question

[justin_willey]

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

[ximen]

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

[ximen]

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

[ximen]

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]

[VolkerBarth]

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.