[Q]:CASCADE OR RECURSIVE TRIGGERS

  Hi,
How can I solve the problem:
I have to do somthing like this

CREATE TABLE SELF_REF(
ID INTEGER NOT NULL PRIMARY KEY,
REF INTEGER REFERENCES SELF_REF(ID),
COLOR CHAR(20));

INSERT INTO SELF_REF VALUES (1,NULL,'RED');
INSERT INTO SELF_REF VALUES (2,1,'RED');
INSERT INTO SELF_REF VALUES (3,1,'RED');
INSERT INTO SELF_REF VALUES (4,2,'RED');
INSERT INTO SELF_REF VALUES (5,2,'RED');

that is

(1,RED)<--(2,RED)<--(4,RED)

        |         |
        |          -(5,RED)
         -(3,RED)

CREATE TRIGGER PAINTER UFTER UPDATE COLOR ON SELF_REF FOR EACH ROW WHEN(OLD.REF <> NULL)
BEGIN
  UPDATE SELF_REF SET COLOR = :NEW.COLOR WHERE ID = :OLD.REF END; PAINTER repainting the branch :

UPDATE SELF_REF SET COLOR = 'WHITE' WHERE ID = 5; which makes

(1,WHITE)<--(2,WHITE)<--(4,RED)

          |           |
          |            -(5,WHITE)
           -(3,RED)

UPDATE SELF_REF SET COLOR = 'BLACK' WHERE ID = 4; which makes

(1,BLACK)<--(2,BLACK)<--(4,BLACK)

          |           |
          |            -(5,WHITE)
           -(3,RED)

------------------------------------------------------------------------

Possibly it is not the best programming style, but object-oriented. I failed to do this in ORACLE 7 and INFORMIX-OL 6.

QUESTIONS : 1 IS IT POSSIBLE IN ANY RDBMS ???
   (ORACLE, INFORMIX, INGRES, MS SQL, SYBASE, INTERBASE, ....???) 2 IS EVERETHING ABOVE CORRECT ?

Any help is appriciated.
Thanks, Alexander.

Alexander Dokumentov E-mail: juice_at_mechanics.msk.su Moskow, RUSSIA Received on Fri Nov 11 1994 - 18:22:24 CET