Rules triggered by rules

Rules triggered by rules

Post by Gregory Seidm » Sun, 12 Jan 2003 07:41:23



Consider the following (contrived) tables, view, and rule:

        CREATE TABLE People (
                uid SERIAL not null,
                first varchar(255) not null,
                last varchar(255) not null,
                primary key (uid)
        );

        CREATE TABLE Attributes (
                uid integer not null REFERENCES People(uid),
                tattooed boolean not null default false,
                alive boolean not null default true,
                primary key (uid)
        );

        CREATE RULE AttributeRow AS ON INSERT TO People DO (
                INSERT INTO Attributes(uid)
                VALUES (COALESCE(NEW.uid, currval('people_uid_seq')));
        );

        CREATE VIEW LongPeople AS (
                SELECT p.*, a.tattooed, a.alive
                FROM People AS p JOIN Attributes AS a ON p.uid = a.uid
        );

I would like to add a rule for inserting into the view. The question is
whether or not the AttributeRow rule will be triggered. Actually, a better
question is probably when will the AttributeRow rule be triggered?

If I can figure out how to make AttributeRow only insert if a row with the
right uid doesn't exists, I think I can get around the issue by inserting
into the Attributes table first, but I'm not entirely sure. I'm also not
sure how reasonable/efficient the following modified AttributeRow rule is
(I'm pretty sure of its correctness):

        CREATE RULE AttributeRow AS ON INSERT TO People DO (
                INSERT INTO Attributes(uid)
                SELECT COALESCE(NEW.uid, currval('people_uid_seq'))
                WHERE NOT EXISTS (
                        SELECT * FROM Attributes
                        WHERE uid = COALESCE(NEW.uid, currval('people_uid_seq'))
                )
        );

So those are the two questions:

1) When will AttributeRow be executed when triggered by an ON UPDATE DO
   INSTEAD rule for LongPeople?

2) Is the modified AttributeRow rule above reasonable or is there a better
   way?

--Greg

---------------------------(end of broadcast)---------------------------

 
 
 

1. One Big Rule or Many Small Rules

Is it generally better to have one large rule that fires a procedure
with many "if" statements in it.  Or to have many rules with "where"
conditions that each fire smaller procedures?  I would guess that if
some of the "where" conditions only occur rarely while other "where"
conditions occur frequently using a large procedure would tend to waste
space in the QSF pool.  However, I think there is some overhead involved
when Ingres decides which rules need to be fired.  Has anyone done any
benchmarks?  If not, is there any interest in any results I might obtain
in this area?

-- Jeff Horn

2. DTS Question

3. Soft Rules Rule System Beta

4. Array of Tables

5. Does SQL 2000 offer Server based Rules similar to the Rules Wizard

6. retrieving identity fields efficiently from dao

7. why there is cost when I am running with RULE hint or optimizer_goal=RULE

8. How to activate SQL Server Agent through DMO?

9. Procdures, triggers, alerters and rules

10. RULE vs TRIGGER

11. Uniqueness of rule, constraint, and trigger names

12. trigger vs rules.

13. Are Rules and Triggers fired during slow BCP?