Re: Rules vs Triggers

# rgp(at)systame(dot)com / 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