I don't know if this can be done in a better way. We need to change referencial integrity for a foreign key in *the same table*.

When changing field value in a child row, all table gets corrupted, with a random number of identical rows created. There is a small video attached showing how to reproduce.

-- CREATE TEST DATABASE
CREATE TABLE "SRSERIES"
(
"Codigo" SMALLINT DEFAULT 0 NOT NULL DESCRIPTION 'Código',
"Nombre" VARCHAR(40) COLLATE "ANSI",
"SerieRectificativa" SMALLINT DEFAULT 0 NOT NULL,
CONSTRAINT "PrimaryKey" PRIMARY KEY ("Codigo")
)!

CREATE TRIGGER "BeforeUpdateCodigo" BEFORE UPDATE OF "Codigo" ON "SRSERIES"
BEGIN
 EXECUTE IMMEDIATE 'UPDATE SRSERIES SET SerieRectificativa = ? WHERE SerieRectificativa = ?' USING NEWROW.CODIGO, OLDROW.CODIGO;
END!

-- IMPORT TESTING DATA
EXECUTE IMMEDIATE 'INSERT INTO "SRSERIES" VALUES (1, ''Default'',  3)';

EXECUTE IMMEDIATE 'INSERT INTO "SRSERIES" VALUES (3, ''Second'', 0)';
Tiago Ameller
Menorca Zeros i Uns SL
www.sistemasc.net

Tiago


Congratulations -- you've found recursion

I have a problem. I'm not sure that you've given us the code as its being used - the last two EXECUTE IMMEDIATE statements don't seem to live anywhere.

Looking at the trigger you should not use the EXECUTE IMMEDIATE there. What that does is spin up another instance of a query and fires it off - I'd guess that's what's causing the recursion. What you need to do is just SET the NEWROW values directly eg

IF (OLDROW.SerieRectificativa IS NULL) OR (OLDROW.SerieRectificativa <> NEWROW.Codigo) THEN
SET  NEWROW.SerieRectificativa = NEWROW.Codigo;
END IF;


Roy Lambert

Thanks for your response, Roy.

The two EXECUTE IMMEDIATE statements just fill the table with sample records, as you could enter them by hand.

I'm aware of the recursion. Problem here is the change has to be made in other rows, not in the one which is being modified. Row A (and, perhaps, C and D) mention the primary key of row B (the one I'm changing the key). It's a simple case of changing a foreign key, but in the same table.

We have solved this in our application, making a wizard to change this value controller at app level, not at DB level.

I reported here due it seems a bug, one caused by a bad programming practice -I know-, which causes a table corruption. After repairing the table, random rows disappear and just original ones remain.

Roy Lambert wrote:

Tiago


Congratulations -- you've found recursion

I have a problem. I'm not sure that you've given us the code as its being used - the last two EXECUTE IMMEDIATE statements don't seem to live anywhere.

Looking at the trigger you should not use the EXECUTE IMMEDIATE there. What that does is spin up another instance of a query and fires it off - I'd guess that's what's causing the recursion. What you need to do is just SET the NEWROW values directly eg

IF (OLDROW.SerieRectificativa IS NULL) OR (OLDROW.SerieRectificativa <> NEWROW.Codigo) THEN
SET  NEWROW.SerieRectificativa = NEWROW.Codigo;
END IF;


Roy Lambert
Tiago Ameller
Menorca Zeros i Uns SL
www.sistemasc.net

Tiago

>I reported here due it seems a bug, one caused by a bad programming practice -I know-, which causes a table corruption. After repairing the table, random rows disappear and just original ones remain.


I agree with you about it being bad programming practice but I don't think its a bug. Its just something that can happen in any programming language.

Try this - preferably wrapped in a transaction with the alteration of Codigo just to be on the safe side.


CREATE TRIGGER "fkUpdate" AFTER UPDATE OF "Codigo" ON "SRSERIES"
BEGIN
EXECUTE IMMEDIATE 'UPDATE SRSERIES SET SerieRectificativa = ? WHERE SerieRectificativa = ?'  USING NEWROW.Codigo, OLDROW.Codigo;
END

The differences are I've moved the trigger to an AFTER UPDATE (can't see it ever being wanted on insert) and with a condition of the Codigo being updated. The EXECUTE IMMEDIATE should never cause the trigger to be called for the updating of SerieRectificativa.

Sorry if I'm teaching granny to suck eggs

Roy

On 5/10/2022 11:09 AM, Tiago Ameller wrote:
> When changing field value in a child row, all table gets corrupted, with a random number of identical rows created. There is a small video attached showing how to reproduce.
>
> -- CREATE TEST DATABASE
> CREATE TABLE "SRSERIES"
> (
> "Codigo" SMALLINT DEFAULT 0 NOT NULL DESCRIPTION 'Código',
> "Nombre" VARCHAR(40) COLLATE "ANSI",
> "SerieRectificativa" SMALLINT DEFAULT 0 NOT NULL,
> CONSTRAINT "PrimaryKey" PRIMARY KEY ("Codigo")
> )!
>
> CREATE TRIGGER "BeforeUpdateCodigo" BEFORE UPDATE OF "Codigo" ON "SRSERIES"
> BEGIN
>    EXECUTE IMMEDIATE 'UPDATE SRSERIES SET SerieRectificativa = ? WHERE SerieRectificativa = ?' USING NEWROW.CODIGO, OLDROW.CODIGO;
> END!
>
> -- IMPORT TESTING DATA
> EXECUTE IMMEDIATE 'INSERT INTO "SRSERIES" VALUES (1, ''Default'',  3)';
>
> EXECUTE IMMEDIATE 'INSERT INTO "SRSERIES" VALUES (3, ''Second'', 0)';

I'm little late to this but i'm somewhat confused how this would even do
anything as shown.

Table has a BEFORE UPDATE trigger and sql is adding records (insert) so
that trigger would not even run in this case

What am i missing ?

Raul

Raul


>What am i missing ?

The fact that we weren't given enough information

The fact that the INSERT statements are disconnected from the trigger and we don't have the code that shows what is being done. Obviously once an update is carried out chaos reigns.

Roy

Roy Lambert wrote:

> The fact that we weren't given enough information

I'm sorry to disagree: If you want to reproduce it, just create the database and import (or create manually) two records. Then, doing that is shown in the video, chaos occurs. You don't need extra code, just edit on data grid.

We have fixed that, doing it as Roy suggested. I posted this due I think if you can corrupt a table just writing (bad) code, it needs to be addressed. So the ElevateDB team can decide if fix or not. Your product, your rules
Tiago Ameller
Menorca Zeros i Uns SL
www.sistemasc.net

Tiago


>> The fact that we weren't given enough information
>
>I'm sorry to disagree: If you want to reproduce it, just create the database and import (or create manually) two records. Then, doing that is shown in the video, chaos occurs. You don't need extra code, just edit on data grid.

One reason I hate writing documentation is that I know what the system should do - its obvious isn't it?

>We have fixed that, doing it as Roy suggested. I posted this due I think if you can corrupt a table just writing (bad) code, it needs to be addressed. So the ElevateDB team can decide if fix or not. Your product, your rules

I wish it could be done simply but I can't see a simple way to do it. Well I can see one way - get rid of EXECUTE IMMEDIATE. Tim has given us this tool and its very useful, very powerful but also dangerous. I not sure there is any programming language that doesn't allow the unwary to write disastrous code. I, personally, more than once have crashed a computer and screwed data up - generally with a backup to hand (phew)

What EXECUTE IMMEDIATE does is take the FREE TEXT parameter you've given it, create a query and pass that text to its sql parameter. That text can be typed in or generated by code in the script which makes it difficult to check for problems.

Taking the specific problem you encountered you'd have to

1. parse the text and identify the table involved and compare with the triggers table
2. check at which point the trigger should be processed
3. parse the text and identify which columns (if any) an being affected
4. determine if any of the above may cause this trigger to refire

This is for just one EXECUTE IMMEDIATE now remember that SQL/PSM is a programming language and that you may also need to test for IF, REPEAT, CASE etc

Sorry for ranting

Roy

On 5/18/2022 3:27 AM, Tiago Ameller wrote:

> I'm sorry to disagree: If you want to reproduce it, just create the database and import (or create manually) two records. Then, doing that is shown in the video, chaos occurs. You don't need extra code, just edit on data grid.

I tried and I'm not really able to reproduce it here (ebdmanager 2.36
b1) so must be missing something still.

My point was that "before update" trigger will not fire for inserts

Raul