Hello

I want to add a foreign key with automatic delete, but didn't succed so far.
e.g. delete a customer, deletes all orders.

Thanks

Thaler,

You cannot do that with ElevateDB, at this point in time. ElevateDB
currently only allows "Restrict"  or "No Action". You would have to do
that in your BeforeDelete code.

var CustomerNumber: String;
begin
if tblCustomerOrders.RecordCount > 0 then
  begin
    CustomerNumber := IntToStr(tblCustomersCustomerNumber.Value);
    tblCustomerOrders.IndexName := 'CustomerNumber';
    tblCustomerOrders.First;
    tblCustomerOrders.SetRange ([CustomerNumber], [CustomerNumber]);
    tblCustomerOrders.ApplyRange;
    while not tblCustomerOrders.EOF do
       begin
         tblCustomerOrders.Delete;
       end;
    tblCustomerOrders.CancelRange;
  end else
    begin
       ShowMessage('There are no orders for this customer.');
    end;
end;

--
Jan Ferguson


Thaler Matthias wrote:

> I want to add a foreign key with automatic delete, but didn't succed
> so far.  e.g. delete a customer, deletes all orders.

Thaler

Or in an AfterDelete trigger eg


TRIGGER "CustomerAfterDelete"
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM Orders WHERE fkCustomer = '+CAST(OLDROW._CustomerID AS VARCHAR);
END

Roy Lambert

Thanks for your suggestions.

To Roy: I'm not sure wheter to take before-delete of after-delete ?
Should I add a transaction ?

Greets Matthias

MatthiasThaler

>To Roy: I'm not sure wheter to take before-delete of after-delete ?
>Should I add a transaction ?

Without knowing details of your app I can only say how I'd do it.

If you select BeforeDelete then there is a chance that the customer isn't deleted so you'd need some sort of exception handling. AfterDelete the customer has gone so "all" you need to do is clean up the orders. This is where it could get tricky. Firstly I don't know whether you just have an order or an order header and associated order lines. The principle is pretty much the same but if the latter will need a few more lines of code.

It might be possible for someone to be editing an order at the time you're deleting a customer. If you don't have a lock on the customer the delete of the customer will work but when you try to delete the order there may be an edit lock in place which could prevent the delete succeeding. To cope with that you need something like

DECLARE LoopControl BOOLEAN;

SET LoopControl = TRUE;

WHILE LoopControl DO
BEGIN
EXECUTE IMMEDIATE 'DELETE FROM Orders WHERE fkCustomer = '+CAST(OLDROW._CustomerID AS VARCHAR);
SET LoopControl = FALSE;
EXCEPTION
END;
END WHILE;

Totally untested of course so use at your own risk

If you have order header and order lines then if the customer code is stored at order line you can just add one line to zap those, if not I'd probably use a cursor to get the order headers and a loop to zap associated lines. Again I probably wouldn't bother with a transaction, just keep trying until I get them.

Something you'll need to give thought to is wether or not there's a chance of a deadlock developing.

Roy Lambert [Team Elevate]

Matthias,

<< To Roy: I'm not sure wheter to take before-delete of after-delete ?
Should I add a transaction ? >>

Start a transaction on just the two tables in the BEFORE DELETE trigger:

CREATE TRIGGER BeforeDeleteTrigger BEFORE DELETE ON Master
BEGIN
   START TRANSACTION ON TABLES Master, Detail;
END

commit the transaction after the delete:

CREATE TRIGGER AfterDeleteTrigger AFTER DELETE ON Master
BEGIN
   COMMIT;
END

be sure to roll it back if there's an error using an ERROR trigger:

CREATE TRIGGER ErrorDeleteTrigger ERROR DELETE ON Master
BEGIN
   ROLLBACK;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Matthias,

Duh, I forgot to add a mention of the actual deletes:

delete the rows and commit the transaction after the delete:

CREATE TRIGGER AfterDeleteTrigger AFTER DELETE ON Master
BEGIN
   EXECUTE IMMEDIATE 'DELETE FROM Detail WHERE MasterID='+OLDROW.ID;
   COMMIT;
END

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim


That's neat. I never knew transactions could span triggers like that.

Roy Lambert

Thanks, I will try that.

Roy,

<< That's neat. I never knew transactions could span triggers like that. >>

Sure, the triggers act just like engine-level
BeforePost/AfterPost/OnPostError event handlers.

--
Tim Young
Elevate Software
www.elevatesoft.com