Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB SQL » View Thread |
Messages 1 to 2 of 2 total |
oldrow.id returning incorrect value in cascading trigger |
Sun, Nov 14 2010 2:04 AM | Permanent Link |
Brandon Rock | In my database, three tables exist that are triggering a strange behavior:
1. The first table is a lookup table. For the sake of simplicity, it's structure has only two fields: table_lookup-- id: Integer; item_name: varchar(100); 2. The second is a log table, for keeping track of where in the database the lookup table is used: table_log-- id: Integer; table_name: varchar(100); table_id: Integer; 3. The third is a self-referencing table that also contains a reference to the lookup table (#1 above). The purpose of the self-reference on this table is that of a template: If a parent record is updated, those same changes will be cascaded to any child records using triggers. The table definition is as follows: table_cascade-- id: Integer; parent_id: Integer; lookup_id: Integer; The problem is occurring in the "After Delete" triggers. There are two "After Delete" triggers defined for this table. The first one, which seems to be working fine, deletes any child records: EXECUTE IMMEDIATE 'DELETE FROM table_cascade WHERE parent_id = ?' USING oldrow.id; The second deletes any log entries. This is where the problem occurs. Although both the parent and the child records are successfully deleted from the first trigger, oldrow.id happens to return the wrong value for the child record when it is deleted. It returns the id value of the parent record for both deletes: EXECUTE IMMEDIATE 'DELETE FROM table_log' + #13 + #10 + 'WHERE table_name = ?' + #13 + #10 + 'AND table_id = ?;' USING 'table_cascade', oldrow.id; |
Tue, Nov 16 2010 9:42 AM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | Brandon,
<< In my database, three tables exist that are triggering a strange behavior: >> Could you send me your actual database ? I want to make sure that I've got the relationships between the rows correct. I think I can see where the issue is, but I want to make sure that I run against your data to ensure that I've got it fixed. -- Tim Young Elevate Software www.elevatesoft.com |
This web page was last updated on Saturday, May 4, 2024 at 12:54 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |