JH:
> > [...]
> > I tried to implement the following rule: if someone wants to delete a
record
> > from a table t_xyz (id integer, deleted boolean) the record should get a
> > delete-flag (deleted = true). When this "pre-deleted" record is deleted
for
> > the next time it should be physically deleted from the database.
> > [...]
TL:
> In my experience, anything you want to do that can be expressed as
> an operation or condition on an individual target tuple of an
> INSERT/UPDATE/DELETE is best done in a trigger, for reasons of both
> performance and understandability. Rules are good for things that
> involve conditions on multiple tuples.
> [...]
I am afraid, that I do not really understand this: if I insert one record in
a view there also is only ONE tuple involved, isn't it? By the way, I admit
that my example is not really useful :-) It should only demonstrate the use
of rules for the book I am writing on.
Further I do not understand the following:
> You'd probably have better luck doing this with a trigger. With this
> rule, the DELETE query expands into two operations, which can
> be written
> as:
>
> UPDATE t_xyz SET deleted = true
> WHERE id IN
> (SELECT old.id FROM t_xyz old WHERE old.id = 1 AND
> old.deleted = false);
>
> DELETE FROM t_xyz WHERE id = 1 AND NOT (deleted = false);
What would have happened, if I executed an unconditional DELETE?
=> DELETE FROM t_xyz;
Which statement would have been generated by PostgreSQL in this case?
Best Regards, Jens
-----------------------------------------------------
T-Systems
Projektleiter
debis Systemhaus GEI GmbH
Hausanschrift: Eichhornstraße 3, 10785 Berlin
Postanschrift: 10785 Berlin
Telefon: (004930) 25 54-32 82
Telefax: (004930) 25 54-31 87
Mobiltelefon: (0170) 167 26 48
E-Mail: jens.hartwig@t-systems.de
Internet: http://www.t-systems.de