This question is probably very easy to answer (does not require Tim), hopefully soemone
will be around to answer it on the weekend.

I am trying to create a tigger to make some changes before the insert takes place. Got
confused with the syntax. How to build a statement to include some new field values? These
are what I have tried, but was not accepted by the engine:


BEGIN
EXECUTE IMMEDIATE   'Update State
                  Set Sort_ID= Sort_ID + 1
                  where Parent_ID = ' + NewRow.Parent_ID  +
                  ' and Sort_ID >= '+ NewRow.Sort_ID;

END     

BEGIN
EXECUTE IMMEDIATE   'Update State
                  Set Sort_ID= Sort_ID + 1
                  where Parent_ID = ' + cast(NewRow.Parent_ID as char(12))  +
                  ' and Sort_ID >= '+ cast(NewRow.Sort_ID as char(12));

END   

Regards,
Leslie

Solved it:

BEGIN
Declare Parent_ID_as_String VARCHAR DEFAULT '';
Declare Sort_ID_as_String VARCHAR DEFAULT '';
                     
set Parent_ID_as_String = cast(NewRow.Parent_ID as VARCHAR);
set Sort_ID_as_String    = cast(NewRow.Sort_ID as VARCHAR);

Execute immediate
                 'Update State
                  Set Sort_ID= Sort_ID + 1
                  where Parent_ID = ' + Parent_ID_as_String +
                  ' and Sort_ID >= '+ Sort_ID_as_String;

END

Also it better be an After Insert trigger.

Very simple sytnax question again:

How to make a string quoted  with   '   in a script?

Leslie wrote:

> Very simple sytnax question again:
>
> How to make a string quoted  with   '   in a script?

Try:

   'Leslie''s string'

--

Malcolm

I probably  wasn't precise enough.

What I want to do is assigning a value to a variable, and the value should be a quoted
string.

Declare aStringVar VarChar;

-- instead of
set aStringVar = 'somestring'

-- I need
set aStringVar = ' 'somestring' '

Any idea about this?



Leslie

 

Leslie,

<< What I want to do is assigning a value to a variable, and the value
should be a quoted string. >>

A QUOTEDSTR() function will be in the either the next build or 2.03.  (most
likely the next build along with an INTRANSACTION() function since both are
really needed right now)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<< (most
likely the next build along with an INTRANSACTION() function since both are
really needed right now) >>

Any chance to see next build / release before Thanksgiving day?

Tiago,

<< Any chance to see next build / release before Thanksgiving day? >>

No. (says he on December 1st)

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim,

<<No. (says he on December 1st) >

Oh, sorry! I forgot to say 2009's Thanksgiving day <very great !