Firebird Trigger

[PJW]

This question is probably more suited for a Firebird forum, but I'll give it a try here anyway.

I have two tables.

COUNTRIES - Contains ISO Country Information  (ISO3166-1)
STATES       - Contains ISO Subdivision Level information (ISO3166-2

There is a foreign key relationship between the two tables on the ISO3 field.  I have it set to Cascade updates to the states file in the unlikely event that the ISO3 code is changed.  The cascade is working properly and all appropriate subdivision records are updated (Deletes are not allowed).

However I have another field called "dependancy", which is a similar relationship but instead refers to the country file itself for those "countries" that are dependencies of other countries (EG Puerto Rico or American Samoa for the US).  To be complete, should the ISO3 field change the DEPENDANCY field should also change in all the dependent country rows.

How would you handle this?    Yes, I could do it in my code, but it seems more appropriate to have a DB Trigger do it.   I created the following trigger, but it does not seem to work.  As far as I can tell it's not getting executed.

CREATE TRIGGER TR_COUNTRYISO3 FOR COUNTRIES ACTIVE
AFTER UPDATE POSITION 0
AS BEGIN
   IF (NEW.ISO3 <> OLD.ISO3) THEN
   BEGIN
     UPDATE COUNTRIES SET DEPENDANCY = NEW.ISO3 WHERE DEPENDANCY = OLD.ISO3;
   END
END;

[rvk]

How about a foreign key to the same table? (Or is that not possible, I though it was)

So

Code: MySQL  [Select][+][-]

1. alter table COUNTRIES
2.   add constraint FK_COUNTRIES_DEPENDANCY
3.   foreign key (DEPENDANCY) references COUNTRIES(ISO3) on update CASCADE

Or does that not work?

[PJW]

I tried that.

The problem is, not all countries will have dependencies.   In fact, most do not, so there are a large number with null
It seems to cause Firebird to bugcheck during the update with an internal consistency error.

[rvk]

It seems to cause Firebird to bugcheck during the update with an internal consistency error.

Mmm, I just checked. My database also has a few foreign keys pointing to the same table. Also with lots of NULL values. But I never use an update CASCADE because I have a unique ID (BIGINT) to which I'm pointing at, so there is no need to update anything if something changes.

But if you get an internal consistency error this would seem like a bug in Firebird (otherwise it shouldn't allow this construction). What version are you using? Did you try it with the latest 2.1.7 (build 18553) or 2.5.4 (build 26856) version (or even the release candidate of 3.0)? Maybe it's something that's fixed in the latest releases.

If you have additional triggers on that table, they could also cause trouble. If there are no triggers and this is just a "simple" table this problem should not occur.

Edit: Possible related issue:
http://tracker.firebirdsql.org/browse/CORE-3925

[rvk]

F.Y.I. Besides the fact the foreign key on the same table with update CASCADE should work... your trigger-code seems correct. I tested a similar trigger in my database and it works. So maybe there is something else wrong (maybe in your code with transactions etc.). Did you try changing an ISO3 in a DB-manager (like FlameRobin or IBExpert) and see if it works then (after committing the transaction)?