Re: [Q]:CASCADE OR RECURSIVE TRIGGERS

Alexander A. Dokumentov (juice_at_mechanics.msk.su) wrote:
: Hi,
: How can I solve the problem:
: I have to do somthing like this

I think it shouldn't be to hard in Ingres. I'll write the INGRES-statement below yours:

: CREATE TABLE SELF_REF(
: ID INTEGER NOT NULL PRIMARY KEY,
: REF INTEGER REFERENCES SELF_REF(ID),
: COLOR CHAR(20));
CREATE TABLE SELF_REF (
ID INTEGER NOT NULL NOT DEFAULT,
REF INTEGER,
COLOR CHAR(20));
MODIFY SELF_REF TO BTREE ON ID;
/* add a DB procedure and a rule for the referential integrity, if you need it */

: 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');
/* stays the same */

: 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;
CREATE PROCEDURE repaint (ID = INTEGER NOT NULL, NEW_COLOR = CHAR(20)) = BEGIN
    UPDATE SELF_REF SET COLOR = :NEW_COLOR WHERE ID = :ID; END;
CREATE RULE repaint AFTER UPDATE ON SELF_REF(COLOR) EXECUTE PROCEDURE repaint (ID = OLD.REF, NEW_COLOR = NEW.COLOR);

: 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)
 
: ------------------------------------------------------------------------
 

: QUESTIONS :
 
: 1 IS IT POSSIBLE IN ANY RDBMS ???
: (ORACLE, INFORMIX, INGRES, MS SQL, SYBASE, INTERBASE, ....???)
The above INGRES statement should be all you need.

: 2 IS EVERETHING ABOVE CORRECT ?
Surely, why not?

: Any help is appriciated.
: Thanks, Alexander.

Hope it helps,

        Christopher

-- 
____________________________________________________________________________
Christopher Etz            Kopernikusstr. 28          D-65929 Frankfurt/Main
cetz_at_cetz.rhein-main.de    Tel.: +49 69 318091        Telefax: +49 69 318091