1

I've been having the hardest time trying to create a trigger for a Sybase SAP SQLAnywhere 16 database following these docs.

Currently it's -131 syntax erroring on line 15/16 (the update statement), but I don't see any error in the syntax. What am I doing wrong here?

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;
5
  • 1
    why have you wrapped the following in double quotes: 1) datetime datatype (twice), 2) coalesce function (twice) and 3) today function? as for the update you've flagged as having an issue ... have you taken the values for the @checkID and @parentRef variables and manually run the same update by itself and if so, does it work or does it also get flagged with a syntax error? could you update the question with the complete error message
    – markp-fuso
    Feb 16, 2021 at 0:12
  • Also - you can't spread DECLARE statements around like that - they have to go immediately after the BEGIN - see dcx.sap.com/1200/en/dbreference/declare-statement.html
    – Justin W
    Feb 16, 2021 at 2:56
  • Additionally I don't think the syntax allows multiple variables to be declared in a single DECLARE statement if they have different data types: DECLARE variable-name [, ... ] data-type [ { = | DEFAULT } initial-value ]
    – Justin W
    Feb 16, 2021 at 11:18
  • 1
    @JustinW thank you for the insight. I should add that there is some backstory here. Somehow, there's an existing trigger in production that is written in t-sql/Sybase ASE type syntax that I need to replicate to create a new automation to our systems. However @@version returns only "16.0.0.1915", which is indicative of the database being SQLAnywhere 16. So I'm not sure how that trigger is working, but I get syntax errors when I try to copy/modify it for this new purpose, and this is my attempt to translate that into SQLAnywhere syntax and get it to work.
    – boog
    Feb 16, 2021 at 13:09
  • @markp-fuso hello again. You may (or may not) remember that you had participated in my last question regarding this same exact issue. It's still not resolved, however I was able to make progress past the "order clause" that was the focus of the last question. Going to follow up on your suggestions here and hopefully make some more progress. Thanks for the help once again.
    – boog
    Feb 16, 2021 at 13:11

1 Answer 1

0

I think the problem you are running into is caused by getting a mixture of T-SQL (ASE) syntax and SQLA (Watcom) syntax. You can use T-SQL triggers in SQLA - but with quite a lot of limitations. It better to do a re-write if you can.

This complies, whether it works, I have no idea! (The changes are all in the DECLARE statements. One per variable, double quotes removed from the timestamp datatype (as per markp-fuso's comment). If you really need local variables further down the code, you have to declare them at the beginning of a new BEGIN / END block)

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;
3
  • Hey I really appreciate your effort on this...as this has been an ongoing issue for me for some time now. That's great, I was able to work with this to get a solution working. Thanks again. @JustinW
    – boog
    Feb 16, 2021 at 19:52
  • 1
    No problem. If you are working with SQL Anywhere there is a very helpful (and friendly) dedicated forum at sqlanywhere-forum.sap.com
    – Justin W
    Feb 16, 2021 at 21:10
  • 1
    BTW You only need to double-quote identifiers if they have spaces in them or duplicate reserved words. Some editors add them automatically, I tend to remove them for readability.
    – Justin W
    Feb 16, 2021 at 21:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.