![]() | ![]() Products ![]() ![]() ![]() ![]() |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 7 of 7 total |
![]() |
Fri, Feb 14 2014 8:40 PM | Permanent Link |
Barry | 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. |
Tue, Feb 18 2014 11:17 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Feb 19 2014 6:22 PM | Permanent Link |
Barry | 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 |
Fri, Feb 28 2014 5:35 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Fri, Feb 28 2014 6:26 PM | Permanent Link |
Barry | 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 |
Tue, Mar 11 2014 7:46 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. ![]() | 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 |
Wed, Mar 12 2014 11:11 AM | Permanent Link |
Barry | "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 |
This web page was last updated on Wednesday, August 14, 2024 at 07:31 PM | Privacy Policy![]() © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? ![]() |