[SQL] [TRIGGER] Returning values from BEFORE UPDATE trigger, without using them
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. -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
[SQL] howto delete using a join ?
Hi, is there a way to delete from a table using information from another table to decide if a row should be dropped? In my case there is a log events ( event_id, event_type_fk, ...); event_types ( event_type_id, relevance_level ); Now I'd like to delete all old events with certain relevance_levels but not all! This works: delete from events where event_id in ( select event_id from events join event_types on event_type_id = event_type_fk where relevance_level in ( 1, 3, 5, 7) and create_ts < '2010/01/01' ); The following doesn't work but is there a more direct way which doesn't involve a subselect? delete from events join event_types on event_type_id = event_type_fk where relevance_level in ( 1, 3, 5, 7); -- Sent via pgsql-sql mailing list ([email protected]) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-sql
