為 Sybase SQLAnywhere 16 創建觸發器

按照這些文件，我一直在嘗試為 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;

我認為您遇到的問題是由 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;

引用自：https://dba.stackexchange.com/questions/285456