Hello all,

please be patient with my not so good English.


I am just evaluating ElevateDB (2.11 Build 3) that looks really good for us in almost all situations.

During the testing I stopped at a point where a primary key in a master table is updated and this must also be pushed to a child table - and I don't know how (currently only working in the ElevateDB Manager, so far nothing done by Delphi code). I searched the documentation and this forum, but could not find something really helpful for my problem. So I hope for your support.

The situation - there are 2 tables:
dCUSTOMERS (primary key name 'PKKunCode' = field 'KunCode', varchar(30))
dCUSTOMERCONTACTS (primary key = field 'KunCode', varchar(30) + field 'Idx', varchar(30))

There is a foreign key in dCUSTOMERCONTACTS:
Target table = dCUSTOMERS
Target constraint = PKKunCode
On Update = No action (how must this be understood?)
On Delete = No action (how must this be understood?)

Everything looks fine. I can only add a record to dCUSTOMERCONTACTS when a corresponding record already exists in dCUSTOMERS.

Now it might happen that the customer number will be changed, e.g. from '4711' to '4711A'.

The record in dCUSTOMERS cannot be saved due to a foreign key violation. Yes, I know this is correct, because the new field value '4711A' must also be saved in the dCUSTOMERCONTACTS table as part of the primary key.

But how to achieve this?

I tried:
a trigger After Update (foreign key violation)
a trigger Before Update (foreign key violation)

My syntax in the trigger is as follows:
EXECUTE IMMEDIATE 'UPDATE dCUSTOMERCONTACTS
     SET dCUSTOMERCONTACTS.KunCode=''' + NEWROW.KunCode + '''
     WHERE dCUSTOMERCONTACTS.KunCode=''' + OLDROW.KunCode + '''';

Now I am absolutely unsure about:
Is the SQL statement for the trigger correct?
To achieve the desired result: is a trigger the correct way - or is there another solution (what is the trigger property 'Load update'?)
Is there a way to "cascade" field value modifications to child tables?

Thank you very much in advance for any hints.
Joe

Joe,

<< I tried:
a trigger After Update (foreign key violation)
a trigger Before Update (foreign key violation)

My syntax in the trigger is as follows:
EXECUTE IMMEDIATE 'UPDATE dCUSTOMERCONTACTS
     SET dCUSTOMERCONTACTS.KunCode=''' + NEWROW.KunCode + '''
     WHERE dCUSTOMERCONTACTS.KunCode=''' + OLDROW.KunCode + ''''; >>

A Before Update trigger is what you want.  If you can send me your database
(edbdatabase.edbcat and all table files (*.edbtbl, *.edbidx, *.edbblb)), I
will take a look and see what is going wrong.  Please send it to:

timyoung@elevatesoft.com

and please indicate whether you're using ANSI or Unicode, as well as any
customizations done to the encryption, signatures, file extensions, etc.

<< To achieve the desired result: is a trigger the correct way - or is there
another solution (what is the trigger property 'Load update'?) >>

That type of trigger is used with replication:

http://www.elevatesoft.com/manual?action=viewtopic&id=edb2sql&topic=Replication

<< Is there a way to "cascade" field value modifications to child tables? >>

Not automatically, no.  This functionality is still waiting on nested
transaction support in order to be implemented.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Hello Tim,

thank you for your quick answer. The database files are on the way...

Have a nice weekend!
Joe

Tim,

I'm afraid that wont work, not because of the syntax but because it still would violate the FK constraint.
(As it should - you did implement the NO ACTION referential action correctly .

--
Fernando Dias
[Team Elevate]

Joe,

<< On Update = No action (how must this be understood?)>>

It means that if the referential constraint is violated, then no actions are performed, that is, the update statement being executed will be aborted and no changes are going to be done in the database.
That's exactly what's going on - you specified a NO ACTION referential constraint, and the constraint is being violated when you change the primary key in the main table, so EDB is reacting exactly as it should - NO ACTION is performed - the update is aborted.

<<To achieve the desired result: is a trigger the correct way - or is there another solution (what is the trigger property 'Load update'?) >>

You can't, unless you drop the Foreign Key constraint.
As for the LOAD UPDATE, it's a type of triggers used with replication only, I think it's not what you are looking for by now.

<<Is there a way to "cascade" field value modifications to child tables?>>

Not automatically, CASCADING referential actions are not implemented in EDB.
But, *you can simulate it with a trigger*, as long as you drop the foreign key constraint, and in that case your before update trigger is going to do what you want .

--
Fernando Dias
[Team Elevate]

Fernando,

<< I'm afraid that wont work, not because of the syntax but because it still
would violate the FK constraint. (As it should - you did implement the NO
ACTION referential action correctly  >>

Yep, you're right.  This is what I told Joe via email:

============================================================

I'm sorry, but Fernando in the forums is correct.  This can't be corrected
with a trigger without removing the FK constraint.

I was thinking of a case without the FK, and was mistaken in my response.

So, unfortunately, you'll have to wait until the cascade option is added to
the engine before you can do such operations with the RI intact.  The other
option is to provide a specific "change primary key" option in your
software, whereby you add a new row that is a copy of the existing row,
except for a new primary key, update the child rows to match the new primary
key, and then delete the old row.  You can do this in a transaction in EDB,
so as to make the whole thing serialized and atomic.

If you have any other questions, please let me know.

Tim Young
Elevate Software
www.elevatesoft.com

Tim and Fernando,

thank you for your responses and explanations.

I will think about and see and decide how to proceed further...

Best regards
Joe

Joe

This is off topic a bit & is just my personal practice, but personally I don't think a true primary key value should ever be edited as you suggest (412345 changing to 412345A).

Better to hold a true primary key in the DB controlled by EDB/your database alone and not visible to a user of your software. Create a client-editable "CustomerCode" field if you like ... for them to search on etc., they can then edit it freely to any value they like (and you can provide a default = the primary key) but don't use it for data integrity/database functionality.

In such a case the existing RI mechanisms in EDB would work fine.

Adam,

thank your for your suggestion. Yes, this is exactly what I am just thinking about....

Best regards
Joe