Re: Rules vs Triggers

[Roman Neuhauser <neuhauser@xxxxxxxxxx>]

# rgp@xxxxxxxxxxx / 2005-07-26 17:53:35 -0400:
> Read the Rules section of the manual and the section on Rules vs Triggers.
> 
> From what I get triggers are necessary for column constraints. As far as
> speed, it seems there are some differences between how fast rules/triggers
> would do the same action, but that some complex analysis is involved to
> determine this. And I gathered rules are necessary to allow
> update/insert/delete actions on views.
> 
> Can anyone give me some simple reasons why they choose rules over triggers
> in their real-world dbs?

    Something like this will ensure the user will not be able to modify
    the author information in updatedon/updatedby columns:

    CREATE TABLE t1 (
        id SERIAL,
        val TEXT,
        updatedon TIMESTAMP,
        updatedby TEXT
    );

    CREATE VIEW v1 AS SELECT * FROM t1;

    CREATE RULE v1i AS ON INSERT TO v1 DO INSTEAD
     INSERT INTO t1 (val, updatedon, updatedby)
      VALUES (NEW.val, NOW(), CURRENT_USER);

    CREATE RULE v1u AS ON UPDATE TO v1 DO INSTEAD
     UPDATE t1 SET
      val = NEW.val,
      updatedon = NOW(),
      updatedby = CURRENT_USER
     WHERE id = NEW.id;

    (That should be taken as pseudocode, I'm sure there are bugs in it.)

    Another common reason is the need/desire to keep values of certain
    columns somehow synchronized, as in:

    CREATE FUNCTION UNIXTS_TO_SQLTS(INTEGER) RETURNS TIMESTAMP AS ...;

    CREATE TABLE t2 (
        id SERIAL,
        unixts INTEGER,
        sqlts TIMESTAMP
    );

    CREATE VIEW v2 AS SELECT * FROM t2;

    CREATE RULE v2i AS ON INSERT TO v2 DO INSTEAD
     INSERT INTO t2 (unixts, sqlts)
      VALUES (NEW.unixts, UNIXTS_TO_SQLTS(NEW.unixts);

    CREATE RULE v2u AS ON UPDATE TO v2 DO INSTEAD
     UPDATE t2 SET
      unixts = NEW.unixts,
      sqlts = UNIXTS_TO_SQLTS(NEW.unixts),
     WHERE id = NEW.id;

    So basically, it's these reasons:
    
    * to have updatable views
      - so you don't select from view_x, but insert into table_x;
      - if updating certain view involves updating more than one table,
        you'll want to have the code fixated in a rule to tighten the
        space where clients can screw up

    * to prevent clients from updating certain columns and/or rows

    * to enforce certain characteristics of data

-- 
How many Vietnam vets does it take to screw in a light bulb?
You don't know, man.  You don't KNOW.
Cause you weren't THERE.             http://bash.org/?255991

---------------------------(end of broadcast)---------------------------
TIP 9: In versions below 8.0, the planner will ignore your desire to
       choose an index scan if your joining column's datatypes do not
       match