Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 9 of 9 total |
Self reference in a BEFORE UPDATE of PRIMARY KEY trigger causes table corruption |
Tue, May 10 2022 11:09 AM | Permanent Link |
Tiago Ameller Menorca Zeros i Uns SL | 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 Attachments: edb_bug.webm |
Wed, May 11 2022 3:26 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, May 12 2022 2:37 PM | Permanent Link |
Tiago Ameller Menorca Zeros i Uns SL | 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 |
Fri, May 13 2022 2:34 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Fri, May 13 2022 8:58 PM | Permanent Link |
Raul Team Elevate | 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 |
Sat, May 14 2022 7:41 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Wed, May 18 2022 3:27 AM | Permanent Link |
Tiago Ameller Menorca Zeros i Uns SL | 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 |
Wed, May 18 2022 7:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, May 19 2022 8:50 AM | Permanent Link |
Raul Team Elevate | 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 |
This web page was last updated on Saturday, April 27, 2024 at 08:52 PM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |