1

I'm using Sybase ASE version 16.0.0.1915, (follows t-sql syntax) and trying to create a new trigger. This new trigger emulates another existing trigger, however is slightly modified. It would be a whole lot harder to modify the existing trigger to address both "workflows", so copying into a new trigger with modified details and an order of "2" would be my preferred solution.

I'm reading in the docs, that in order to create a new trigger based on the same action, I need to set the order for the triggers to fire off. If no order is specified, it is assigned order of 0 (first). Docs

So here is my trigger, the existing similar trigger does not have an order set. When I place "ORDER 2" into the create statement (after the name, before "on" in row 1), I keep getting syntax error -131.

I've also tried placing the "ORDER 2" after the table name, before "for", and after "insert", before "as". All producing the same error -131.

So what am I doing wrong? Is there some other problem with this trigger preventing me to use the ORDER clause?

create trigger "DBA"."WKM_autoFillCL143" on
"DBA"."case_checklist" for insert
as
if((select top 1 "inserted"."code" from "inserted") in( '143' ) )
  begin
    declare @parentRef integer,@desc varchar(255),@desc1 varchar(255),@checkID integer
    set @parentRef = (select top 1 "parent_ref" from "inserted")
    if(@parentRef <> '0')
      begin
        set @desc = (select "description" from "case_checklist" where "checklist_id" = @parentRef)
        if(@desc is not null)
          begin
            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)
              begin
                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
          end
      end
  end
4
  • @BrentOzar says: The Silent Bug I Find in Most Triggers you don't take into account multiple rows in inserted – Charlieface Jan 8 at 1:46
  • SAP SQLAnywhere 16 v16.0.0.1915, from what I gather @markp-fuso – boog Jan 12 at 20:33
  • Which correlates to these docs, showing ORDER coming before "on" (where I originally placed it), which still doesn't work. Oy vey. dcx.sap.com/sa160/en/dbreference/create-trigger-statement.html – boog Jan 12 at 20:35
  • there are a lot of differences in the create trigger syntax between ASE and SQLAnywhere ... net result being your current (ASE) create trigger command has quite a few syntax issues (as far as SQLAnywhere is concerned); I've consolidated my comments (+ fix for current syntax issue) into an answer; if you still have problems with the syntax I'd suggest starting a new question ... tagged for sybase-sql-anywhere ... and including your SQLAnywhere-specific create trigger command; this Q&A would need a complete rewrite and is probably not worth the effort ... – markp-fuso Jan 12 at 21:13
1

There are 4x different RDBMS products under the Sybase name ... Adaptiver Server Enterprise (ASE), SQLAnywhere, IQ and Advantage. The 4x products do not share a common SQL language/syntax.

The question references a link for the create trigger syntax in ASE.

The sample code in the question appears to also be for ASE.

However, from the comments we've discovered the OP is actually using the SQLAnywhere product (version 16.0.0.1915). And while SQLAnywhere does provide some half-workable support for ASE's T-SQL dialect, it does not include a common syntax for the create trigger command.

From the comments: OP has located the documentation for SQLAnyhere's create trigger command.

Using one of the examples from that link (scroll down to find CREATE TRIGGER myTrig), and modifying OP's current trigger code to match the documentation, I'm thinking OP's looking for something like:

create trigger "DBA"."WKM_autoFillCL143"
after insert order 4 on "DBA"."case_checklist"
REFERENCING NEW AS inserted
FOR EACH STATEMENT
BEGIN
....

NOTES:

  • I work primarily on ASE, and my SQLAnywhere experience is rather limited, so OP may need to tweak the above to work correctly in SQLAnywhere; also ...
  • OP should review the various options available via SQLAnywhere's create trigger command (eg, should the trigger fire before or after the insert?)
  • OP will need to review the rest of the current (ASE/T-SQL) trigger code to see which parts can be used 'as is' in SQLAnywhere and which parts will need to be rewritten to match SQLAnywhere's syntax
  • OP will also want to verify SQLAnywhere's support for quoted identifiers (ie, does the current use of double-quoted identifiers meet SQLAnywhere's syntax?)
0

@mustaccio has already answered your question in the comments: place it just before AS at the top of the trigger.

You have a much bigger problem though. Your trigger does not deal with multi-row inserts. It needs a full rewrite. I hope I've got these joins right, as I don't have your full relational model:

create trigger "DBA"."WKM_autoFillCL143" on
"DBA"."case_checklist" for insert
as

if(not exists(select 1
    from inserted i
    inner join case_checklist c where c.checklist_id = i.parent_ref
    where i.code = '143' and i.parent_ref <> '0' and c.description is not null))
  return

update w
set c143 = i.checklist_id
from inserted i
join WKM_RecordChecklistMapping w on w.c142 = i.parent_ref 
where i.code = '143' and i.parent_ref <> '0' and c.description is not null

update c
set description =
        'Receipt ' + ut.Provider_Name + ' Records? ' + ut.Records_Requested +
        ', dates ' + coalesce(convert(varchar(255), ut.For_Dates_From, 1), '00/00/00') +
        ' to ' + coalesce(convert(varchar(255), ut.Through, 1), '00/00/00'),
    staff_assigned = 'ZKS',
    due_date = (today()+7)
from inserted i
inner join WKM_RecordChecklistMapping w on w.c142 = i.parent_ref 
inner join user_tab2_data ut on ut.tab_id = w.tab_id
inner join case_checklist c on c.checklist_id = i.checklist_id
where i.code = '143' and i.parent_ref <> '0' and c.description is not null

Is parent_ref <> '0' correct? It looks like you actually wanted to write parent_ref is not null. Either way, I hope this is not an int column that you are comparing to text.

5
  • Thanks for your input. The trigger above is not new, it's been in place in production for years now and "working" fine. Management wanted another "workflow" automated, so I copied the existing trigger, changed a few parameters (c140 becomes c142) and now I just need to get it to fire after the original trigger, but still having trouble with that. – boog Jan 12 at 18:17
  • I'm running version 16.0.0.1915, which apparently should support multiple triggers, but I continue to get error -131 wherever I place the "order" clause. I've attempted putting it after "insert"/before "as", after "on"/before {table_name}, and after {trigger_name}/before "on". Not sure what the problem is. – boog Jan 12 at 18:20
  • To answer your last question, "parent_ref <> '0'" should be correct. I believe the default value is 0 of type int. So were just checking to see that "parent_ref" has some value other than 0. – boog Jan 12 at 18:22
  • @boog Depends if it's nullable, then the default is null. The fact it worked for years just means either no-one noticed, or only 1 row inserts were done. That code definitely does not work with multi-row inserts – Charlieface Jan 12 at 20:00
  • it doesn't do multi-row inserts – boog Jan 12 at 20:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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