recursive or cascade trigger?
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@mechanics.msk.su
Moskow, RUSSIA