Received: from maia.hub.org (maia-2.hub.org [200.46.204.251]) by mail.postgresql.org (Postfix) with ESMTP id 836AE63267E for ; Fri, 3 Sep 2010 10:09:38 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.251]) (amavisd-maia, port 10024) with ESMTP id 31132-06 for ; Fri, 3 Sep 2010 13:09:30 +0000 (UTC) X-Greylist: delayed 00:06:06.952126 by SQLgrey-1.7.6 Received: from zarafa14.zmi.at (zarafa1.zmi.at [212.69.164.59]) by mail.postgresql.org (Postfix) with ESMTP id 5109B632629 for ; Fri, 3 Sep 2010 10:09:31 -0300 (ADT) Received: from zarafa14.zmi.at (localhost [127.0.0.1]) by zarafa14.zmi.at (Postfix) with SMTP id BEFC8F972 for ; Fri, 3 Sep 2010 15:03:19 +0200 (CEST) Subject: fail-safe sql update triggers From: =?windows-1252?Q?Michael_Monnerie?= To: pgsql-admin@postgresql.org Date: Fri, 3 Sep 2010 15:03:19 +0200 Mime-Version: 1.0 Content-Type: text/plain; charset=utf-8 Content-Transfer-Encoding: quoted-printable X-Priority: 3 (Normal) X-Mailer: Zarafa 6.30.15-21229 Thread-Index: ActLaGE7yth5dRHmSFe80/zY0jK71Q== Message-Id: X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0 tagged_above=-10 required=5 tests=BAYES_40=-0.001, MIME_QP_LONG_LINE=0.001 X-Spam-Level: X-Archive-Number: 201009/36 X-Sequence-Number: 35580 I want to log all activity from a table to a old_table. Creating an ON INSERT trigger is simple, it just needs to INSERT INTO old_filter SELECT NEW.*; in a procedure that is called via the trigger. But what about updates=3F There is no simple UPDATE old_filter SET NEW.* WHERE id=3DNEW.id; so I would need to declare each column like UPDATE old_filter SET field1=3DNEW.field1,f2=3DNEW.f2,.... where id=3DNEW.id; But that is error prone, because when the "filter" table is changed to have a new column, the UPDATE statement would not contain it. Is there a fail-proof shortcut=3F mit freundlichen Gr=C3=BC=C3=9Fen Michael Monnerie, Ing. BSc it-management Internet Services http://proteger.at [gesprochen: Prot-e-schee] Tel: 0660 / 415 65 31 // Wir haben im Moment zwei H=C3=A4user zu verkaufen: // http://zmi.at/langegg/ // http://zmi.at/haus2009/