syntax error

CREATE OR REPLACE TRIGGER "dba".t_trace.trg_trace_insb
   BEFORE INSERT ON "dba".t_trace
   REFERENCING NEW AS new_t
   FOR EACH ROW
BEGIN
  DECLARE @lProcName          long varchar;
  DECLARE @lRevNo             long varchar;
  IF EXISTS (SELECT x
               FROM sysprocedure  a
                  , new_t
              WHERE a.remarks           IS NOT NULL       
                AND a.remarks         LIKE '*%'
                AND a.proc_name          = SUBSTRING(new_t.trace_text, 0 , CASE WHEN CHARINDEX('(',new_t.trace_text) = 0 THEN LENGTH(new_t.trace_text) ELSE  CHARINDEX('(',new_t.trace_text)-1 END)
                AND new_t.caller         = 'CODE'
                AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED') )
THEN
   BEGIN
             SELECT a.proc_name
            ,REPLACE(a.remarks,'*','')
        INTO @lProcName
            ,@lRevNo 
        FROM sysprocedure  a
            ,new_t
       WHERE a.remarks     IS NOT NULL      
         AND a.remarks LIKE '*%'
         AND a.proc_name = SUBSTRING(new_t.trace_text, 0 , CASE WHEN CHARINDEX('(',new_t.trace_text) = 0 THEN LENGTH(new_t.trace_text) ELSE  CHARINDEX('(',new_t.trace_text)-1 END)
         AND new_t.caller    = 'CODE'
         AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED')
--INSERT INTO dba.t_trace (trace_action,trace_text,caller) SELECT (trace_Action, [@ProcName +' ('+ @RevNo +')'], caller) FROM NEW_t
UPDATE new_t
SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ]
END    
END IF
END

When I run above code, I see erorr as could not execute statement. syntax error near "Update" on line 30 SQLCODE = -131, ODBC 3 State = '42000' Line 1, Column 1

syntax-error sa-10

asked 06 Nov '12, 10:58

roadtrain64
136●6●7●10
accept rate: 0%

edited 06 Nov '12, 11:05

Mark Culp
24.9k●10●141●297

flat view

One Answer:

active answers oldest answers newest answers popular answers

Just a guess.... since it looks like you are using WATCOM SQL syntax, I would say that you are missing a semicolon at the end of this line:

    AND new_t.trace_action IN ('FUNCTION LOADED','SP LOADED')

and before the UPDATE.

You may want to also put a semicolon after this line:

    SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ]

and after the END and END IF and END lines.

permanent link

answered 06 Nov '12, 11:09

Mark Culp
24.9k●10●141●297
accept rate: 41%

edited 06 Nov '12, 11:11

Thanks for the super quick reply. First time creating watcome triggers.I did that change and magic happened but I realized since its before insert trigger. I have commented --UPDATE new_t --SET new_t.trace_text = @lProcName +' ('+ @lRevNo +')' ; AND above that there is insert ststement that i need to include whihc i m using as INSERT INTO dba.t_trace (trace_action,trace_text,caller) SELECT trace_Action, @ProcName +' ('+ @RevNo +')', caller FROM new_t But during execution, I see, table new_t not found.why?

(06 Nov '12, 11:35) roadtrain64

Replies hidden

When using a row-level trigger (and before triggers are always row-level), you are not referencing a table (as in a statement-level trigger), but the "current record". So instead of using an UPDATE statement, you can simply access the column with a SET statement, somewhat like a local variable:

SET new_t.trace_text = [ @lProcName +' ('+ @lRevNo +')' ];

I would recommend to check the docs for the difference between row- and statement-level triggers.

(06 Nov '12, 11:44) Volker Barth

new_t is not a table when you use a "for each row" trigger, contrary to "for each statement" btw. "new_t.trace_action" is just a variable in this case, you don't need to add new_t to the from part to access the values just add "new_t." before the column name.

(06 Nov '12, 11:45) Markus Dütting

So in general, you can omit everything that handles "new_t" as a table in your trigger's code:

omit ", new_t" from all FROM clauses - the contents of the current row to be inserted is directly accessible -
use the above SET statement instead of the UPDATE statement.

That (and possibly further adaptions) should get your code going.

What is the meaning of the INSERT statement? It would insert another entry in the same table (and that would trigger another insert recursively...). - In a BEFORE insert trigger, you usually only modify the values of the row to be inserted (or modify data in other tables).

(06 Nov '12, 16:32) Volker Barth

Thanks a lot for making me understand. Its working now.

(06 Nov '12, 17:56) roadtrain64

Replies hidden

Feel free to show the "corrected code" - surely other folks will have to learn the rich feature set of SQL Anywhere's CREATE TRIGGER statement and the differences between row-level and statement-level triggers, as well... I had, too:)

(06 Nov '12, 18:11) Volker Barth

showing 2 of 6 show all flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-10 ×119
syntax-error ×19

question asked: 06 Nov '12, 10:58

question was seen: 2,133 times

last updated: 06 Nov '12, 18:11

SAP SQL Anywhere

syntax error

Follow this question

Related questions