Hi,

I have a bunch of tables A-->B-->C-->D-->E, where A is master for B, B
is master for C ....

I have BEFORE DELETE triggers for tables A,B,C,D however they don't
work as I would expected, i.e. if I delete a record in table A, I want
detail records in table B to also be deleted and when that happens I
want C detail records to be deleted, etc.

The way it works for me is: if I delete a record in table D, than E
detail records are deleted. And that's about it.

All my triggers look the same (with the obvious table/field name
changes):

BEGIN
 DECLARE TriggerStatement STATEMENT;
 PREPARE TriggerStatement FROM 'DELETE FROM B WHERE Ref=?';
 START TRANSACTION ON TABLES B;
 BEGIN
   EXECUTE TriggerStatement USING OLDROW.A_Ref;
   COMMIT;
   EXCEPTION
     ROLLBACK;
 END;
END


what am I doing wrong?
TIA
Lucian

Lucian,

<< The way it works for me is: if I delete a record in table D, than E
detail records are deleted. And that's about it.>>

What version of EDB are you using ?  There was an issue like this fixed in
2.02 B15:

http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.02&type=f&incident=3018

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young [Elevate Software] wrote:

> Lucian,
>
> << The way it works for me is: if I delete a record in table D, than
> E detail records are deleted. And that's about it.>>
>
> What version of EDB are you using ?  There was an issue like this
> fixed in 2.02 B15:
>
>
http://www.elevatesoft.com/incident?action=viewrep&category=edb&release=2.02&type=f&incident=3018

2.03 build 6 Unicode

Lucian

Lucian,

<< 2.03 build 6 Unicode >>

Please send me the database catalog that you're using.

--
Tim Young
Elevate Software
www.elevatesoft.com

> Please send me the database catalog that you're using.

Done. thanks
Lucian

Lucian,

<< Done. thanks >>

Okay, the issue is that EDB is issuing an exception for the second START
TRANSACTION because you already have a transaction active.  However, this
exception never appears because it is being trapped in the outermost
EXCEPTION block.

You'll have to simply start a transaction for the entire database in each
trigger, but only if a transaction is not already in effect.  You can do a
check on this using the INTRANSACTION() function:

http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=24&topic=510

I hope to modify EDB at some point to allow for multiple concurrent
transactions on various tables, which will make this type of processing a
lot easier.

--
Tim Young
Elevate Software
www.elevatesoft.com

> You can do a check on this using the INTRANSACTION() function:
>
>
http://www.elevatesoft.com/manual?action=mantopic&id=edb2sql&category=24&topic=510


I did that. Still not working. Now I'm getting something lie:

---------------------------
ElevateDB Manager (Unicode)
---------------------------
ElevateDB Error #404 An error occurred with the statement at line 11
and column 13 (Transaction error: This operation cannot be performed
while the database APPS has an active transaction (START TRANSACTION ON
APPS))
---------------------------
OK   
---------------------------


Trigger(s) look like:

TRIGGER "RemoveVersions"
BEGIN
 DECLARE LocalTrans BOOLEAN DEFAULT FALSE;
 DECLARE TriggerStatement STATEMENT;
 PREPARE TriggerStatement FROM 'DELETE FROM Versions WHERE ProdRef=?';
 IF NOT INTRANSACTION('') THEN
   START TRANSACTION ON TABLES Versions;
   SET LocalTrans=TRUE;
 END IF;
 BEGIN
   EXECUTE TriggerStatement USING OLDROW.ProdRef;
   IF LocalTrans THEN COMMIT; END IF;
   EXCEPTION
     IF LocalTrans THEN ROLLBACK; END IF;
     RAISE;
 END;
END

I tried first passing a table name to INTRANSACTION but got the same
thing.


thx,
Lucian

Lucian,

<< I did that. Still not working. Now I'm getting something lie: >>

What does the APPs trigger look like now ? Are you sure that it is the same
as the others ?

I just tested the INTRANSACTION function, and it appears to be working just
fine.

--
Tim Young
Elevate Software
www.elevatesoft.com

Tim Young wrote:

> Lucian,
>
> << I did that. Still not working. Now I'm getting something lie: >>
>
> What does the APPs trigger look like now ? Are you sure that it is
> the same as the others ?
>
> I just tested the INTRANSACTION function, and it appears to be
> working just fine.

I chahnged a bit the name of tables, but anyway, now all before delete
triggers look very simple, they call a procedure. The thing is I'm
having problems with the procedure.

So, the triggers:
 CALL TriggerBeforeDelete('Versions', 'ProdRef', OLDROW.ProdRef);

and the procedure:

PROCEDURE "TriggerBeforeDelete" (
 IN "DetailTableName" VARCHAR(100) COLLATE UNI_CI,
 IN "DetailRefFieldName" VARCHAR(100) COLLATE UNI_CI,
 IN "OLDROW_RecRef" INTEGER)
BEGIN
 DECLARE LocalTrans BOOLEAN DEFAULT FALSE;
 DECLARE TriggerStatement STATEMENT;
 DECLARE TableName VarChar;
 DECLARE FieldName VarChar;
 SET TableName = DetailTableName;
 SET FieldName = DetailRefFieldName;
 PREPARE TriggerStatement FROM 'DELETE FROM '+TableName+' WHERE
'+FieldName+'=?';
 IF NOT INTRANSACTION('') THEN
   START TRANSACTION ON TABLES TableName;
   SET LocalTrans=TRUE;
 END IF;
 BEGIN
   EXECUTE TriggerStatement USING OLDROW_RecRef;
   IF LocalTrans THEN COMMIT; END IF;
   EXCEPTION
     IF LocalTrans THEN ROLLBACK; END IF;
     RAISE;
 END;
END


Something about TableName not in the Default schema ... I'm sure I'm
not using correct those quotes, but can't figure how to do it right.


Thx,
Lucian

Lucian,

<< I chahnged a bit the name of tables, but anyway, now all before delete
triggers look very simple, they call a procedure. The thing is I'm having
problems with the procedure. >>

Ahh, okay.  The problem is with the START TRANSACTION - it does not accept
variables in the ON TABLES clause, so you're going to have to do something
klunky like this:

CASE TableName
WHEN 'Versions' THEN
   START TRANSACTION ON TABLES Versions;
WHEN 'Apps' THEN
   START TRANSACTION ON TABLES 'Apps';
END CASE;

--
Tim Young
Elevate Software
www.elevatesoft.com