Andreas Pflug <[EMAIL PROTECTED]> writes:I'm talking about a real life problem, and the upper sample was just an essence.
Tom Lane wrote:
This can already be done by comparing old and new values, no?No, this is not the case.
UPDATE foo SET x=x, y=y
is different from
UPDATE foo SET y=y
if triggers maintaining x are involved.
Only for what I would call extremely weird semantics of the triggers.
If a trigger preceding yours did the same action (assigned x to itself), would you consider that something you needed to track? If so, how would you find out about it? You couldn't.
If you want me to believe that the above is an important requirement, you'd better convince me that it's sane, because I don't think so.
Consider this:
Table with one column that is maintained by a trigger for this rule:
- Only one row in a group of rows may have a foo-value of "true", all others must be "false".
- If foo=true is inserted/updated, other members of that data group must be set to false.
- If foo=false, designate one row for foo=true
- If not touched, use true if first member of that group, or false
This can be maintained by a trigger, but it will possibly trigger itself recursively (but doesn't need a second recursion)
Now we have another column: ts timestamp, that should contain the timestamp when the row was inserted/updated the last time by the *user*, not the trigger which is considered to work in the background. On INSERT, a DEFAULT current_timestamp will be the selected option, on UPDATE you would use NEW.TS := current_timestamp. But how to update the row, and retain the old timestamp value? Normally, a user's query wouldn't touch the ts column at all, leaving it to the backend to insert the correct values. But in the "maintain foo" trigger case, we could use "SET ts=ts" to signal to the trigger that we explicitely want to set the value. Same applies for the import case, when we want to insert a ts value coming from elsewhere but not from the trigger. This could also be done if there was something like "UPDATE ... WITH OPTION NOTRIGGER(trg_update_timestamp)" or so.
Regards, Andreas
---------------------------(end of broadcast)--------------------------- TIP 2: you can get off all lists at once with the unregister command (send "unregister YourEmailAddressHere" to [EMAIL PROTECTED])
