Re: Rules vs Triggers

Am Dienstag, 26. Juli 2005 23:53 schrieb Randall Perry:
> 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?

Triggers are executed per row, so they are quite procedural. If you insert or
update 500 rows they are fired 500 times.

Rules modify the sql query tree. So rules are at some point nothing else as
rewrites to your sql statement. If you update 500 rows and you have an on
update rule. Your query tree is modified once and gets executed for all 500
rows.

Rules are much faster an much more relational than triggers are, because they
become pure sql before they reach the database.

imagine an on delete trigger which record the deletion in an audit table like
this:

create trigger tg_member before delete on member for each row EXECUTE
PROCEDURE audit_meber_deletion();

audit_meber_deletion() does an INSERT to an audit table.

no think of members are organized in groups. If you delete a group ALL members
are deleted because of cascading foreing keys references.

Now delete a group with 20000 members. The trigger is fired 20000 times

No Imagine a rule which does
create rule rl_member AS ON DELETE TO member
DO
INSERT INTO member_deletion (membername) VALUES (OLD.membername)

this is executed once and is as fast as SQL can be.

Normally you dont see a difference between triggers and rules if you have
update and insert statemnts which affect only a few rows. but if it comes to
affecting many rows, you should use rules. But rules are more difficult to
understand.

kind regards,
janning