主页 / dba / 问题 / 285456
Accepted
boog
Asked: 2021-02-16 12:49:52 +0800 CST

为 Sybase SQLAnywhere 16 创建触发器

  • 772

按照这些文档，我一直在尝试为 Sybase SAP SQLAnywhere 16 数据库创建触发器。

目前它在第 15/16 行（更新语句）上出现 -131 语法错误，但我没有在语法中看到任何错误。我在这里做错了什么？

create trigger "DBA"."WKM_autoFillCL143" after insert order 2 on "DBA"."case_checklist"
when ((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer; 
    declare @desc varchar(255); 
    declare @desc1 varchar(255); 
    declare @checkID integer;
    set @parentRef = (select top 1 "parent_ref" from "inserted");
    if (@parentRef <> '0')
      then
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 "checklist_id" from "inserted");
            update "WKM_RecordChecklistMapping" set "c143" = @checkID where "c142" = @parentRef;
            declare @tabid integer;
            set @tabid = (select top 1 "tab_id" from "WKM_recordChecklistMapping" where "c142" = @parentRef);
            set @tabid = (select top 1 "tab_id" from "user_tab2_data" where "tab_id" = @tabid);
            if (@tabid is not null)
              then
                declare @recProvider varchar(255),@recsRequested varchar(255),@dateFrom "datetime",@dateTo "datetime"
                set @recProvider = (select top 1 "Provider_Name" from "user_tab2_data" where "tab_id" = @tabid);
                set @recsRequested = (select top 1 "Records_Requested" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateFrom = (select top 1 "For_Dates_From" from "user_tab2_data" where "tab_id" = @tabid);
                set @dateTo = (select top 1 "Through" from "user_tab2_data" where "tab_id" = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+"coalesce"(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+"coalesce"(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 "checklist_id" from "inserted");
                update "case_checklist" set "description" = @desc1,"staff_assigned" = 'ZKS',"due_date" = ("today"()+7) where "checklist_id" = @checkID
              end if;
          end if;
      end if;
  end;
trigger sybase-sql-anywhere

1 个回答

  1. Best Answer

    我认为您遇到的问题是由 T-SQL (ASE) 语法和 SQLA (Watcom) 语法的混合引起的。您可以在 SQLA 中使用 T-SQL 触发器 - 但有很多限制。如果可以，最好重新编写。

    这符合，是否有效，我不知道！（更改都在 DECLARE 语句中。每个变量一个，从时间戳数据类型中删除双引号（根据 markp-fuso 的注释）。如果您确实需要在代码的后面进一步使用局部变量，则必须在开头声明它们一个新的 BEGIN / END 块）

    create trigger DBA.WKM_autoFillCL143 after insert order 2 on DBA.Case_Checklist
  when ((select top 1 inserted.code from inserted) in( '143' ) )
  begin
    declare @parentRef integer; 
    declare @desc varchar(255); 
    declare @desc1 varchar(255); 
    declare @checkID integer;
    declare @tabid integer;
    declare @recProvider varchar(255);
    declare @recsRequested varchar(255);
    declare @dateFrom datetime;
    declare @dateTo datetime;
    set @parentRef = (select top 1 parent_ref from inserted);
    if (@parentRef <> '0')
      then
        set @desc = (select description from case_checklist where checklist_id = @parentRef);
        if (@desc is not null)
          then
            set @checkID = (select top 1 checklist_id from inserted);
            update WKM_RecordChecklistMapping set c143 = @checkID where c142 = @parentRef;
            set @tabid = (select top 1 tab_id from WKM_recordChecklistMapping where c142 = @parentRef);
            set @tabid = (select top 1 tab_id from user_tab2_data where tab_id = @tabid);
            if (@tabid is not null)
              then
                set @recProvider = (select top 1 Provider_Name from user_tab2_data where tab_id = @tabid);
                set @recsRequested = (select top 1 Records_Requested from user_tab2_data where tab_id = @tabid);
                set @dateFrom = (select top 1 For_Dates_From from user_tab2_data where tab_id = @tabid);
                set @dateTo = (select top 1 Through from user_tab2_data where tab_id = @tabid);
                set @desc1 = 'Receipt '+@recProvider+' Records? '+@recsRequested+', dates '+coalesce(convert(varchar(255),@dateFrom,1),'00/00/00')+' to '+coalesce(convert(varchar(255),@dateTo,1),'00/00/00');
                set @checkID = (select top 1 checklist_id from inserted);
                update case_checklist set description = @desc1,staff_assigned = 'ZKS',due_date = (today()+7) where checklist_id = @checkID
              end if;
          end if;
      end if;
  end;
    • 0

相关问题