Re: INSERT RULE QUERY ORDER

Justin Tocci <jtocci(at)tlcusa(dot)com> writes:
> Thanks for the reply Tom, here's the rule that works:

> CREATE RULE tquotehistory_update AS ON UPDATE TO vtquotehistory DO INSTEAD
> (

> INSERT INTO tquotehistory_log ("ID", "Item", "Quote1", "DemandCost1",
> "Quote2", "DemandCost2", "DueDate", "POIntoInmass", "Weeks", "QuoteSent",
> "Reference", "Supplier", "TLCProposal", "Counter", "Notes") VALUES
> (old."ID", old."Item", old."Quote1", old."DemandCost1", old."Quote2",
> old."DemandCost2", old."DueDate", old."POIntoInmass", old."Weeks",
> old."QuoteSent", old."Reference", old."Supplier", old."TLCProposal",
> old."Counter", old."Notes");

> UPDATE tquotehistory SET "Item" = new."Item", "Quote1" = new."Quote1",
> "DemandCost1" = new."DemandCost1", "Quote2" = new."Quote2", "DemandCost2" =
> new."DemandCost2", "DueDate" = new."DueDate", "POIntoInmass" =
> new."POIntoInmass", "Weeks" = new."Weeks", "QuoteSent" = new."QuoteSent",
> "Reference" = new."Reference", "Supplier" = new."Supplier", "TLCProposal" =
> new."TLCProposal", "Counter" = new."Counter", "Notes" = new."Notes" WHERE
> (tquotehistory."ID" = old."ID");

> );

> Switch the order and the INSERT doesn't insert a record into the log, but
> the UPDATE updates and there is no error.

Hm. Am I right in supposing that vtquotehistory is a view on
tquotehistory? Does the UPDATE cause the row that was visible in the
view to be no longer visible in the view (or at least not matched by the
constraints on the original UPDATE command)? If so, that's your problem
--- the "old" references in the INSERT will no longer find any matching
row in the view.

If your goal is to log operations on tquotehistory, my recommendation is
to forget about views and rules and just use a trigger on tquotehistory.
Triggers are *way* easier to understand, even if the notation looks
worse.

regards, tom lane