Board index » delphi » Manipulating strings in trigger

Manipulating strings in trigger

Hi,

I am not very well versed in stored procedure / trigger and wondering if
someone could give me pointer on how to create the following.

I have a field CHAR(10) made like A00-00-000 where the 3 last number must be
calculated by the server before posting so the user enter A00-00-*** and I
have to replace the ***. I had this made on the client side before in a
paradox app but I consider that the server should do this either by using a
trigger or SP (My guess is a trigger may be the best place). In any case if
anyone could give me pointers I would appreciate.

When editing the field is read-only,

On insert I need the max of the last three accroding to the the sequence
represented by X00-00-... starting at 1
The field mask on insert lets the user enter the first part and the last
three are filled with *.

Format represent "[A-Z]Year-Month-Sequencial number"

So if I have these

N00-12-001
Z01-03-001
A01-03-001
A01-03-002

The input of A01-03-*** from the field input mask should set the field to
A01-03-003
The input of Z01-03-*** from the field input mask should set the field to
Z01-03-002

TIA
Marc

 

Re:Manipulating strings in trigger


some ideas:
before update / insert trigger
If you need both, they can call a stored procedure.
This code is not transaction-save!

SELECT CAST(MAX(SUBSTR("FIELD",8,10)) AS INTEGER) + 1
 FROM "TABLE" WHERE SUBSTR("FIELD",1,7) = SUBSTR(NEW."FIELD",1,7) INTO
:"TEMP_INT";
IF("TEMP_INT" BETWEEN 0 AND 9) THEN "TEMP_CHAR" = '00' ||CAST("TEMP_INT" AS
CHAR(1));
ELSE IF("TEMP_INT" BETWEEN 10 AND 99) THEN "TEMP_CHAR" = '0' ||
CAST("TEMP_INT" AS CHAR(2));
ELSE IF("TEMP_INT" BETWEEN 100 AND 999) THEN "TEMP_CHAR" = CAST("TEMP_INT"
AS CHAR(3));
NEW."FIELD" = SUBSTR(NEW."FIELD",1,7) || "TEMP_CHAR";

hth,
Jan Henrik Sylvester <m...@janh.de>

"Marc Leclerc" <ml...@crosswinds.net> schrieb im Newsbeitrag
news:3ab642b1_1@dnews...

Quote
> Hi,

> I am not very well versed in stored procedure / trigger and wondering if
> someone could give me pointer on how to create the following.

> I have a field CHAR(10) made like A00-00-000 where the 3 last number must
be
> calculated by the server before posting so the user enter A00-00-*** and I
> have to replace the ***. I had this made on the client side before in a
> paradox app but I consider that the server should do this either by using
a
> trigger or SP (My guess is a trigger may be the best place). In any case
if
> anyone could give me pointers I would appreciate.

Other Threads