Thread: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
[TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
From
Torsten Zühlsdorff
Date:
Hello, i have a non-trival problem and i do not believe that it is solvable. I have defined an BEFORE UPDATE trigger. The trigger catch every update, change some columns of the new row, make an insert of the new row and returns null to abort the update. All fine till here :) Now the problem: if i do an UPDATE table [..] RETURNING * it returns always null. This is correct behavior, because the UPDATE is canceld by the trigger which returns null, so the updated data is "null". But i want to get the new data inserted by the trigger without a new select. Is there a possibility to get the inserted data or do i have to do a select after the update? Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann.
Re: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
From
Torsten Zühlsdorff
Date:
Hey Dmitriy, thanks for your reply. > I think, its would be better to use rule on update instead of the trigger > in such case as you. I've played the whole weekend with the rule-system, but it didn't work for my case. I have a dynamic trigger, which takes cares about revision of rows for every table, it is called from. It looks like that: CREATE OR REPLACE FUNCTION versionizeContent() RETURNS TRIGGER AS $$ BEGIN /* add new version in central register and insert new row */ NEW.revision := addContentRevision (OLD.content_id, OLD.revision, sessval('user_id')::int)); EXECUTE 'INSERT INTO ' || quote_ident(TG_TABLE_NAME) || ' SELECT (' || QUOTE_LITERAL(NEW) || '::' || quote_ident(TG_TABLE_NAME) ||').*' ; RETURN NULL; END; $$ LANGUAGE 'plpgsql' VOLATILE; Even if i drop the dynamic INSERT-Part and write it for every relation, i wasn't able to figured out how to manipulate the NEW-Record. The best i tried so far was: CREATE RULE "versionize" AS ON UPDATE TO templates DO INSTEAD ( SELECT addContentRevision (OLD.content_id, OLD.revision, sessval('user_id')::int) INTO NEW.revision; INSERT INTO templates SELECT NEW.* RETURNING *; ); But an Updates ends with the ERROR: "ERROR: schema "*NEW*" does not exist" Has anyone a hint how to manipulate the NEW record within an RULE? Thanks, Torsten
Torsten Zühlsdorff <foo@meisterderspiele.de> writes: > I have defined an BEFORE UPDATE trigger. The trigger catch every update, > change some columns of the new row, make an insert of the new row and > returns null to abort the update. Why in the world would you do that? Just return the modified row from the trigger and let the update proceed normally. regards, tom lane
Re: [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
From
Torsten Zühlsdorff
Date:
Tom Lane schrieb: > Torsten Zühlsdorff <foo@meisterderspiele.de> writes: >> I have defined an BEFORE UPDATE trigger. The trigger catch every update, >> change some columns of the new row, make an insert of the new row and >> returns null to abort the update. > > Why in the world would you do that? Just return the modified row from > the trigger and let the update proceed normally. Because of content-revision. I have a lot of text which is modified and created from multiple persons. The requierment is, that i need every version of every content (+ their meta-data). And i don't need just a log, which lists the changes. The different revisions of *one* content are used at the *same* time at *different* parts of the website. Therefore i rewrite every UPDATE to an INSERT. And except of the missing returning everything works fine and fast. :) Greetings, Torsten -- http://www.dddbl.de - ein Datenbank-Layer, der die Arbeit mit 8 verschiedenen Datenbanksystemen abstrahiert, Queries von Applikationen trennt und automatisch die Query-Ergebnisse auswerten kann.