Let's say there are 3 tables: Parent, Child, GrandChild.

1) If a row is deleted from Child, it also has to delete the GrandChild rows too, within a single transaction.
2) Likewise, if row is deleted from Parent, the Parent has to delete its rows from Child, which deletes its rows from GrandChild, within a single transaction.

Is there a *reliable* way to do this using triggers and transactions?

Here is one way to do it:

Create Trigger "Parent_BeforeDeleteTrigger" BEFORE DELETE ON "Parent";
begin
 Start Transaction on Tables 'Parent', 'Child', 'GrandChild';
 Execute Immediate 'Delete from Child where Parent_Id=OldRow.Id;
end;

Create Trigger "Parent_AfterDeleteTrigger" AFTER DELETE ON "Parent";
begin
 Commit;
end;

Create Trigger "Child_BeforeDeleteTrigger" BEFORE DELETE ON "Child";
begin
 if not(InTransaction('Child')) and not(InTransaction('GrandChild')) then
   Start Transaction on Tables 'Child', 'GrandChild';
 end if;
 Execute Immediate 'Delete from GrandChild where Parent_Id=OldRow.Id;
end;

Create Trigger "Child_AfterDeleteTrigger" AFTER DELETE ON "Child";
begin
 if not InTransaction('Parent') then  --Is this a valid assumption?
   Commit;
 end if;
end;

So this allows a row to be delete from Child and it will delete its GrandChild rows all wrapped within a transaction started in "Child_BeforeDeleteTrigger".

If a row is deleted from Parent, it will delete its Child rows which in turn deletes its GrandChild rows, all wrapped inside of a single transaction started in "Parent_BeforeDeleteTrigger".

Problem
-----------
The "Child_AfterDeleteTrigger" assumes if there is a transaction on the Parent table, then it was the Parent table that initiated the transaction, and this trigger won't commit the transaction because it assumes the "Parent_AfterDeleteTrigger" will commit the transaction.

This really isn't a valid assumption is it?  Because the transaction could have been created in the "Child_BeforeDeleteTrigger" when its row was deleted and someone else could have the Parent table in a transaction not related to cascading deletes. So the transaction is never committed.

How do I solve this problem?
TIA
Barry

P.S.
In the last 30 seconds of writing this post (why couldn't it have been the first 30 seconds?), I think I might have a solution. But first I want to see if anyone can put on their thinking cap and come up with a better solution.

Barry,

<< Is there a *reliable* way to do this using triggers and transactions? >>

My suggestion would be to wait until nested transactions are available.
They're the next feature on the list, along with OSX support.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

"My suggestion would be to wait until nested transactions are available.
They're the next feature on the list, along with OSX support."

I'd love to. But I need it now.

I think I may have an elegant solution. At least until you or Roy shoots it down.

The problem as I mentioned in my last post is this.
When doing cascading deletes on 3 or more tables (Parent, Child, GrandChild), it difficult to determine who is responsible for committing the transaction. The Child.BeforeDelete trigger can start the transaction then delete the GrandChild rows then the Child.AfterDelete can commit the transaction. But this won't work if the Parent row is being deleted because the Parent.BeforeDelete was the one that issued the transaction, and so on. So the transaction should only be committed by the same table that initiated the transaction.

I think you mentioned in another thread about using temporary tables and using it for defining session variables. This would work, but it takes a bit of code that I'd rather avoid.

I think this might be a simpler solution.

Create a Lock_<tablename> for each of the tables that need cascading deletes inside of a transaction.

So in this example there would be Lock_Parent, Lock_Child, and Lock_GrandChild tables. (Or Lock_Customer, Lock_Invoice, Lock_Detail, etc.) The table has only 1 column and doesn't need any rows. It doesn't matter what the column type is, but I just used an Integer. This is a permanent disk based table and is never dropped.

Here are the triggers that accomplish the cascading deletes (from any number of levels of tables). This sample have the triggers for the 3 tables mentioned above.

-- Parent Table Below --
Create Trigger "Parent_BeforeDeleteTrigger" BEFORE DELETE ON "Parent";
begin
Start Transaction on Tables 'Parent', 'Child', 'GrandChild', 'Lock_Parent';
Execute Immediate 'Delete from Child where Parent_Id=OldRow.Id;
end;

Create Trigger "Parent_AfterDeleteTrigger" AFTER DELETE ON "Parent";
begin
 if InTransaction('Parent') and InTransaction('Lock_Parent') then
   Commit;
 end if;
end;

Create Trigger "Parent_ErrorDelete" ERROR Delete on "Parent"
begin
 if InTransaction('Parent') and InTransaction('Lock_Parent') then
   Rollback;
 else
   Raise;  --Reraise error so proper handler gets it
 end if;
end;

-- Child Table Below --
Create Trigger "Child_BeforeDeleteTrigger" BEFORE DELETE ON "Child";
begin
if not(InTransaction('Child')) and not(InTransaction('GrandChild')) then
  Start Transaction on Tables 'Child', 'GrandChild', 'Lock_Child';
end if;
Execute Immediate 'Delete from GrandChild where Parent_Id=OldRow.Id;
end;

Create Trigger "Child_AfterDeleteTrigger" AFTER DELETE ON "Child";
begin
if InTransaction('Lock_Child') then  --Transaction originated with Child table?
  Commit;
end if;
end;

Create Trigger "Child_ErrorDelete" ERROR Delete on "Child"
begin
 if InTransaction('Child') and InTransaction('Lock_Child') then
   Rollback;
 else
   Raise;     --Reraise the error so proper parent table rolls it back
 end;
end;


So the Lock_<Tablename> acts as a semaphore (flag) to determine which table initiated the transaction. There won't be any more contention on getting a lock on the lock_ tables because they are only used for cascading deletes and the other tables in the transaction would already be locked for cascading deletes.

Well, there it is. Will if fly? Or is it a turkey that is about to be get shot down by the skilled marksman Roy or Tim?

Barry

Barry,

<< I'd love to. But I need it now. >>

I'm going to try to push 2.16 out as soon as possible, so I'll see what I
can do.

<< Well, there it is. Will if fly? Or is it a turkey that is about to be get
shot down by the skilled marksman Roy or Tim? >>

Is the lock table a temporary table ?  If so, then yes, it should work.

Tim Young
Elevate Software
www.elevatesoft.com

Tim,

>Is the lock table a temporary table ?  If so, then yes, it should work.<

No, the lock table is a permanent disk based table.  The lock table only serves as a flag that tells the user which parent table initiated the transaction. Since the child tables are also locked along with the lock table when the parent table starts the transaction, the user will never be prevented from locking the lock table if the child tables are not already in a transaction.

This makes the locking of the lock table superfluous, apart from acting as a flag. That's the beauty of it. No records or temporary tables need to be created. IMHO, it should have lower overhead than using temporary tables that need to be checked to see if they exist, and created if they don't.

Barry

Barry,

<< No, the lock table is a permanent disk based table.  The lock table only
serves as a flag that tells the user which parent table initiated the
transaction. Since the child tables are also locked along with the lock
table when the parent table starts the transaction, the user will never be
prevented from locking the lock table if the child tables are not already in
a transaction.

This makes the locking of the lock table superfluous, apart from acting as a
flag. That's the beauty of it. No records or temporary tables need to be
created. IMHO, it should have lower overhead than using temporary tables
that need to be checked to see if they exist, and created if they don't. >>

Just to confirm: you know what tables are involved when the transaction
starts, it's the commit level that you're worried about ?

Tim Young
Elevate Software
www.elevatesoft.com

"Tim Young [Elevate Software]" wrote:

<Just to confirm: you know what tables are involved when the transaction
starts, it's the commit level that you're worried about ?>

Correct. I need to know which level started the transaction because that will be the level that will commit it.

I figure using a dummy lock table (permanent disk table) would be more efficient than creating a temporary table with a row containing the start level.

Barry