db-derby-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From John English ...@brighton.ac.uk>
Subject Trigger problem
Date Thu, 21 Oct 2010 16:29:02 GMT
I have some tables to model assemblies (of "stuff"). Each assembly
can have several associated tests and resources used by the tests.
The resources, once defined, can be reused when creating new tests
on the same assemblies.

The tables are defined like so:

CREATE TABLE assemblies (
   assembly VARCHAR(15)   NOT NULL,
   title    VARCHAR(255)  NOT NULL,
   CONSTRAINT assemblies _pk  PRIMARY KEY (assembly)
);

CREATE TABLE tests (
   id          INTEGER       GENERATED ALWAYS AS IDENTITY,
   assembly    VARCHAR(15)   NOT NULL,
   title       VARCHAR(255)  NOT NULL,
   CONSTRAINT tests_pk       PRIMARY KEY (id),
   CONSTRAINT tests_1        UNIQUE (assembly, title),
   CONSTRAINT tests_2        FOREIGN KEY (assembly)
                             REFERENCES assemblies(assembly)
                             ON DELETE CASCADE
);

CREATE TABLE resources (
   id          INTEGER       GENERATED ALWAYS AS IDENTITY,
   name        VARCHAR(256)  NOT NULL,
   assembly    VARCHAR(15)   NOT NULL,
   refcount    INTEGER       DEFAULT 0,
   CONSTRAINT resources_pk   PRIMARY KEY (id),
   CONSTRAINT resources_1    UNIQUE (name,assembly)
);

CREATE TABLE resource_usage (
   resid       INTEGER       NOT NULL,
   testid      INTEGER       NOT NULL,
   CONSTRAINT resusage_1     FOREIGN KEY (resid)
                             REFERENCES resources(id)
                             ON DELETE CASCADE
);

The idea is that when a test is modified, the resources it
used to use are deleted from resource_usage and the resources
it now uses are added to resource_usage. I have triggers to
maintain the reference count (refcount) in resources:

CREATE TRIGGER delete_usage
   AFTER DELETE ON resource_usage
   REFERENCING OLD AS del
   FOR EACH ROW MODE DB2SQL
   UPDATE  resources
     SET   refcount=refcount-1
     WHERE id=del.resid;

CREATE TRIGGER insert_usage
   AFTER INSERT ON resource_usage
   REFERENCING NEW AS ins
   FOR EACH ROW MODE DB2SQL
   UPDATE  resources
     SET   refcount=refcount+1
     WHERE id=ins.resid;

Deleting a test fires a trigger to delete all the associated
resource usages:

CREATE TRIGGER delete_test
   AFTER DELETE ON tests
   REFERENCING OLD AS del
   FOR EACH ROW MODE DB2SQL
   DELETE FROM resource_usage
     WHERE xmlid=del.id

Another trigger deletes resources when they are no longer
referenced:

CREATE TRIGGER delete_resource
   AFTER UPDATE ON resources
   REFERENCING OLD AS upd
   FOR EACH ROW MODE DB2SQL
   DELETE FROM resources
     WHERE id=upd.id
     AND   refcount=0;

Now, what happens is that if I delete a test, all works fine.
The associated resource_usage records get deleted by the delete_test
trigger, which in turn decrements the resource refcounts, which in
turn triggers resource deletion if any resources have a reference
count of 0.

If however I delete an assembly, what I get is this:

java.sql.SQLException : An attempt was made to put a data value of type
'java.lang.String' into a data value of type 'INTEGER'.

According to the Derby log file, the last thing that happens is this:

Executing prepared statement: DELETE FROM resource_usage
     WHERE xmlid=CAST 
(org.apache.derby.iapi.db.Factory::getTriggerExecutionContext().getOldRow().getObject(1) 
AS INTEGER)

Now, getObject(1) in all the tables except "assemblies" will return an
integer. What I think is happening is that when I say "delete from 
assemblies where assembly=?", the assembly deletion cascades to delete
the associated tests, and this in turn fires the delete_test trigger;
however, the "REFERENCING OLD AS del" clause refers to the original
"delete from assemblies" query, not to the cascaded test deletion.

Can anyone:
(a) confirm my reasoning?
(b) suggest a workaround?

Many thanks,

----------------------------------------------------------------------
  John English              | mailto:je@brighton.ac.uk
  Senior Lecturer           | http://www.it.bton.ac.uk/staff/je
  School of Computing & MIS | "Those who don't know their history
  University of Brighton    |  are condemned to relive it" (Santayana)
----------------------------------------------------------------------

Mime
View raw message