Received: from mail-ob01.one.net.au (mail-ob01.one.net.au [61.12.0.161]) by hub.org (8.10.1/8.10.1) with ESMTP id e93CHUr25592 for ; Tue, 3 Oct 2000 08:17:31 -0400 (EDT) Received: Received: [from darthvader.e-magine.com.au (async171-syd-isp-237.nas.one.net.au [61.12.15.172]) by mail-ob01.one.net.au with ESMTP id e93CGc822837; Tue, 3 Oct 2000 22:16:42 +1000 (EST)] Message-Id: <4.3.2.7.0.20001003233932.00b66768@mail.e-magine.com.au> X-Sender: e-magine@mail.e-magine.com.au X-Mailer: QUALCOMM Windows Eudora Version 4.3.2 Date: Tue, 03 Oct 2000 23:41:52 +1000 To: Carolyn Lu Wong , "pgsql-sql@postgresql.org" From: Keith Wong Subject: Re: trigger examples In-Reply-To: <39D98534.31BC8197@kss.net.au> References: <39D96C98.9C7A6A55@kss.net.au> <39D9636B.5B45952C@smartt.com> Mime-Version: 1.0 Content-Type: multipart/mixed; boundary="=====================_15354859==_" X-Archive-Number: 200010/19 --=====================_15354859==_ Content-Type: text/plain; charset="us-ascii"; format=flowed This is an example script I use to keep a log of all database operations on my tables. Hope it helps. At 05:05 PM 3/10/2000 +1000, Carolyn Lu Wong wrote: >drfrog@smartt.com wrote: > > > > Carolyn Lu Wong wrote: > > > > > > I need to write a trigger to create a new record in log table if it's a > > > new record or if column A and B changes. Can someone give me an example > > > how to do this. > > > > > > Are there any sites that gives trigger examples?? > > > > > > Thanks. > > > > the fulltextindex in the contrib dir gives an example > >The example uses C. Is it possible to create the trigger with plpsql >function? --=====================_15354859==_ Content-Type: application/octet-stream; name="table_customer.sql" Content-Transfer-Encoding: base64 Content-Disposition: attachment; filename="table_customer.sql" ZHJvcCBzZXF1ZW5jZSBzZXFfY3VzdG9tZXJfa2V5OwpjcmVhdGUgc2VxdWVu Y2Ugc2VxX2N1c3RvbWVyX2tleSBzdGFydCAxIGluY3JlbWVudCAxOwoKZHJv cCB0YWJsZSBDdXN0b21lcjsKY3JlYXRlIHRhYmxlIEN1c3RvbWVyIAooCglj dXN0b21lcl9pZCAJCWludDQgCQlub3QgbnVsbCBkZWZhdWx0IG5leHR2YWwo J3NlcV9jdXN0b21lcl9rZXknKSwKCWN1c3RvbWVyX3RpdGxlCQl2YXJjaGFy KDMpCW5vdCBudWxsLAoJZmlyc3RfbmFtZQkgCQl2YXJjaGFyKDUwKQlub3Qg bnVsbCwKCW1pZGRsZV9pbml0aWFsIAkJY2hhcigxKSwKCWxhc3RfbmFtZQkJ IAl2YXJjaGFyKDUwKSBub3QgbnVsbCwKCXN0cmVldF9hZGRyZXNzCQl2YXJj aGFyKDcwKSBub3QgbnVsbCwKCWNpdHkJCQkJdmFyY2hhcig1MCkgbm90IG51 bGwsCglzdGF0ZQkJCQl2YXJjaGFyKDUwKSBub3QgbnVsbCwKCXBvc3RfY29k ZQkJCXZhcmNoYXIoMTIpIG5vdCBudWxsLAoJY291bnRyeQkJCQl2YXJjaGFy KDUwKSBub3QgbnVsbCwKCWhvbWVfcGhvbmUJCQl2YXJjaGFyKDIwKSBub3Qg bnVsbCwJCglhbHRfcGhvbmUJCQl2YXJjaGFyKDIwKSwJCQoJZW1haWwJCQkJ dmFyY2hhcig3MCkgbm90IG51bGwgdW5pcXVlLAkJCQoJcGFzc3dvcmQJCQl2 YXJjaGFyKDEyKSBub3QgbnVsbCwJCQkJCgljdXN0b21lcl9zdGF0dXMJCWNo YXIoMSkgCW5vdCBudWxsIGRlZmF1bHQgJ2EnLAkJCQkJCgljcmVhdGVfdGlt ZXN0YW1wCXRpbWVzdGFtcCAJbm90IG51bGwgZGVmYXVsdCBub3coKSwJCgls YXN0X3VwZGF0ZSAJCXRpbWVzdGFtcCAJbm90IG51bGwgZGVmYXVsdCBub3co KSwKCXVwZGF0ZWRfYnkJCQl2YXJjaGFyKDEyKSBub3QgbnVsbCwKCXByaW1h cnkga2V5CQkJKGN1c3RvbWVyX2lkKQopOwoKZHJvcCBzZXF1ZW5jZSBzZXFf YXVkaXRjdXN0b21lcl9rZXk7CmNyZWF0ZSBzZXF1ZW5jZSBzZXFfYXVkaXRj dXN0b21lcl9rZXkgc3RhcnQgMSBpbmNyZW1lbnQgMTsKCQpkcm9wIHRhYmxl IEF1ZGl0Q3VzdG9tZXI7CmNyZWF0ZSB0YWJsZSBBdWRpdEN1c3RvbWVyIAoo CglhdWRpdF9rZXlfaWQgCQkJaW50NCAJCWRlZmF1bHQgbmV4dHZhbCgnc2Vx X2F1ZGl0Y3VzdG9tZXJfa2V5Jykgbm90IG51bGwsCgljdXN0b21lcl9pZCAJ CQlpbnQ0LAoJY3VzdG9tZXJfdGl0bGUJCQl2YXJjaGFyKDMpLAoJZmlyc3Rf bmFtZQkgCQkJdmFyY2hhcig1MCksCgltaWRkbGVfaW5pdGlhbCAJCQljaGFy KDEpLAoJbGFzdF9uYW1lCQkgCQl2YXJjaGFyKDUwKSwKCXN0cmVldF9hZGRy ZXNzCQkJdmFyY2hhcig3MCksCgljaXR5CQkJCQl2YXJjaGFyKDUwKSwKCXN0 YXRlCQkJCQl2YXJjaGFyKDUwKSwKCXBvc3RfY29kZQkJCQl2YXJjaGFyKDEy KSwKCWNvdW50cnkJCQkJCXZhcmNoYXIoNTApLAoJaG9tZV9waG9uZQkJCQl2 YXJjaGFyKDIwKSwJCglhbHRfcGhvbmUJCQkJdmFyY2hhcigyMCksCQkKCWVt YWlsCQkJCQl2YXJjaGFyKDcwKSwJCQkKCXBhc3N3b3JkCQkJCXZhcmNoYXIo MTIpLAkJCQkKCWN1c3RvbWVyX3N0YXR1cwkJCWNoYXIoMSksCQkJCQkKCWNy ZWF0ZV90aW1lc3RhbXAJCXRpbWVzdGFtcCwJCglsYXN0X3VwZGF0ZSAJCQl0 aW1lc3RhbXAsCgl1cGRhdGVkX2J5CQkJCXZhcmNoYXIoMTIpLAoJYXVkaXRf bG9nX3RpbWUgCQkJdGltZXN0YW1wIAlkZWZhdWx0IG5vdygpLAoJYXVkaXRf b3BlcmF0aW9uX3R5cGUgCWNoYXIoMSksCglwcmltYXJ5IGtleQkJCQkoYXVk aXRfa2V5X2lkKQopOwoKZHJvcCBmdW5jdGlvbiB0cF9jdXN0b21lcl9hX2lu cygpOwpjcmVhdGUgZnVuY3Rpb24gdHBfY3VzdG9tZXJfYV9pbnMoKSAKcmV0 dXJucyBvcGFxdWUgYXMKCSdiZWdpbgoJCWluc2VydCBpbnRvIEF1ZGl0Q3Vz dG9tZXIgCgkJCShjdXN0b21lcl9pZCwKCQkJIGN1c3RvbWVyX3RpdGxlLAoJ CQkgZmlyc3RfbmFtZSwKCQkJIG1pZGRsZV9pbml0aWFsLAoJCQkgbGFzdF9u YW1lLAoJCQkgc3RyZWV0X2FkZHJlc3MsCgkJCSBjaXR5LAoJCQkgc3RhdGUs CgkJCSBwb3N0X2NvZGUsCgkJCSBjb3VudHJ5LAoJCQkgaG9tZV9waG9uZSwJ CgkJCSBhbHRfcGhvbmUsCQkKCQkJIGVtYWlsLAkJCQoJCQkgcGFzc3dvcmQs CQkJCQoJCQkgY3VzdG9tZXJfc3RhdHVzLAkJCQkJCgkJCSBjcmVhdGVfdGlt ZXN0YW1wLAkKCQkJIGxhc3RfdXBkYXRlLAoJCQkgdXBkYXRlZF9ieSwKCQkJ IGF1ZGl0X29wZXJhdGlvbl90eXBlKQoJCXZhbHVlcwoJCQkobmV3LmN1c3Rv bWVyX2lkLAoJCQkgbmV3LmN1c3RvbWVyX3RpdGxlLAoJCQkgbmV3LmZpcnN0 X25hbWUsCgkJCSBuZXcubWlkZGxlX2luaXRpYWwsCgkJCSBuZXcubGFzdF9u YW1lLAoJCQkgbmV3LnN0cmVldF9hZGRyZXNzLAoJCQkgbmV3LmNpdHksCgkJ CSBuZXcuc3RhdGUsCgkJCSBuZXcucG9zdF9jb2RlLAoJCQkgbmV3LmNvdW50 cnksCgkJCSBuZXcuaG9tZV9waG9uZSwJCgkJCSBuZXcuYWx0X3Bob25lLAkJ CgkJCSBuZXcuZW1haWwsCQkJCgkJCSBuZXcucGFzc3dvcmQsCQkJCQoJCQkg bmV3LmN1c3RvbWVyX3N0YXR1cywJCQkJCQoJCQkgbmV3LmNyZWF0ZV90aW1l c3RhbXAsCQoJCQkgbmV3Lmxhc3RfdXBkYXRlLAoJCQkgbmV3LnVwZGF0ZWRf YnksCgkJCSAnJ2knJyk7CgkJcmV0dXJuIG5ldzsKCWVuZDsnCmxhbmd1YWdl ICdwbHBnc3FsJzsgCmNyZWF0ZSB0cmlnZ2VyIHRyX2N1c3RvbWVyX2FfaW5z IAphZnRlciBpbnNlcnQgb24gQ3VzdG9tZXIKZm9yIGVhY2ggcm93IGV4ZWN1 dGUgcHJvY2VkdXJlIHRwX2N1c3RvbWVyX2FfaW5zKCk7Cgpkcm9wIGZ1bmN0 aW9uIHRwX2N1c3RvbWVyX2FfdXBkKCk7CmNyZWF0ZSBmdW5jdGlvbiB0cF9j dXN0b21lcl9hX3VwZCgpIApyZXR1cm5zIG9wYXF1ZSBhcwoJJ2JlZ2luCgkJ aW5zZXJ0IGludG8gQXVkaXRDdXN0b21lciAKCQkJKGN1c3RvbWVyX2lkLAoJ CQkgY3VzdG9tZXJfdGl0bGUsCgkJCSBmaXJzdF9uYW1lLAoJCQkgbWlkZGxl X2luaXRpYWwsCgkJCSBsYXN0X25hbWUsCgkJCSBzdHJlZXRfYWRkcmVzcywK CQkJIGNpdHksCgkJCSBzdGF0ZSwKCQkJIHBvc3RfY29kZSwKCQkJIGNvdW50 cnksCgkJCSBob21lX3Bob25lLAkKCQkJIGFsdF9waG9uZSwJCQoJCQkgZW1h aWwsCQkJCgkJCSBwYXNzd29yZCwJCQkJCgkJCSBjdXN0b21lcl9zdGF0dXMs CQkJCQkKCQkJIGNyZWF0ZV90aW1lc3RhbXAsCQoJCQkgbGFzdF91cGRhdGUs CgkJCSB1cGRhdGVkX2J5LAoJCQkgYXVkaXRfb3BlcmF0aW9uX3R5cGUpCgkJ dmFsdWVzCgkJCShuZXcuY3VzdG9tZXJfaWQsCgkJCSBuZXcuY3VzdG9tZXJf dGl0bGUsCgkJCSBuZXcuZmlyc3RfbmFtZSwKCQkJIG5ldy5taWRkbGVfaW5p dGlhbCwKCQkJIG5ldy5sYXN0X25hbWUsCgkJCSBuZXcuc3RyZWV0X2FkZHJl c3MsCgkJCSBuZXcuY2l0eSwKCQkJIG5ldy5zdGF0ZSwKCQkJIG5ldy5wb3N0 X2NvZGUsCgkJCSBuZXcuY291bnRyeSwKCQkJIG5ldy5ob21lX3Bob25lLAkK CQkJIG5ldy5hbHRfcGhvbmUsCQkKCQkJIG5ldy5lbWFpbCwJCQkKCQkJIG5l dy5wYXNzd29yZCwJCQkJCgkJCSBuZXcuY3VzdG9tZXJfc3RhdHVzLAkJCQkJ CgkJCSBuZXcuY3JlYXRlX3RpbWVzdGFtcCwJCgkJCSBuZXcubGFzdF91cGRh dGUsCgkJCSBuZXcudXBkYXRlZF9ieSwKCQkJICcndScnKTsKCQlyZXR1cm4g bmV3OwoJZW5kOycKbGFuZ3VhZ2UgJ3BscGdzcWwnOyAKY3JlYXRlIHRyaWdn ZXIgdHJfY3VzdG9tZXJfYV91cGQgCmFmdGVyIHVwZGF0ZSBvbiBDdXN0b21l cgpmb3IgZWFjaCByb3cgZXhlY3V0ZSBwcm9jZWR1cmUgdHBfY3VzdG9tZXJf YV91cGQoKTsKCmRyb3AgZnVuY3Rpb24gdHBfY3VzdG9tZXJfYV9kZWwoKTsK Y3JlYXRlIGZ1bmN0aW9uIHRwX2N1c3RvbWVyX2FfZGVsKCkgCnJldHVybnMg b3BhcXVlIGFzCgknYmVnaW4KCQlpbnNlcnQgaW50byBBdWRpdEN1c3RvbWVy IAoJCQkoY3VzdG9tZXJfaWQsCgkJCSBjdXN0b21lcl90aXRsZSwKCQkJIGZp cnN0X25hbWUsCgkJCSBtaWRkbGVfaW5pdGlhbCwKCQkJIGxhc3RfbmFtZSwK CQkJIHN0cmVldF9hZGRyZXNzLAoJCQkgY2l0eSwKCQkJIHN0YXRlLAoJCQkg cG9zdF9jb2RlLAoJCQkgY291bnRyeSwKCQkJIGhvbWVfcGhvbmUsCQoJCQkg YWx0X3Bob25lLAkJCgkJCSBlbWFpbCwJCQkKCQkJIHBhc3N3b3JkLAkJCQkK CQkJIGN1c3RvbWVyX3N0YXR1cywJCQkJCQoJCQkgY3JlYXRlX3RpbWVzdGFt cCwJCgkJCSBsYXN0X3VwZGF0ZSwKCQkJIHVwZGF0ZWRfYnksCgkJCSBhdWRp dF9vcGVyYXRpb25fdHlwZSkKCQl2YWx1ZXMKCQkJKG9sZC5jdXN0b21lcl9p ZCwKCQkJIG9sZC5jdXN0b21lcl90aXRsZSwKCQkJIG9sZC5maXJzdF9uYW1l LAoJCQkgb2xkLm1pZGRsZV9pbml0aWFsLAoJCQkgb2xkLmxhc3RfbmFtZSwK CQkJIG9sZC5zdHJlZXRfYWRkcmVzcywKCQkJIG9sZC5jaXR5LAoJCQkgb2xk LnN0YXRlLAoJCQkgb2xkLnBvc3RfY29kZSwKCQkJIG9sZC5jb3VudHJ5LAoJ CQkgb2xkLmhvbWVfcGhvbmUsCQoJCQkgb2xkLmFsdF9waG9uZSwJCQoJCQkg b2xkLmVtYWlsLAkJCQoJCQkgb2xkLnBhc3N3b3JkLAkJCQkKCQkJIG9sZC5j dXN0b21lcl9zdGF0dXMsCQkJCQkKCQkJIG9sZC5jcmVhdGVfdGltZXN0YW1w LAkKCQkJIG9sZC5sYXN0X3VwZGF0ZSwKCQkJIG9sZC51cGRhdGVkX2J5LAoJ CQkgJydkJycpOwoJCXJldHVybiBvbGQ7CgllbmQ7JwpsYW5ndWFnZSAncGxw Z3NxbCc7IApjcmVhdGUgdHJpZ2dlciB0cl9jdXN0b21lcl9hX2RlbCAKYWZ0 ZXIgZGVsZXRlIG9uIEN1c3RvbWVyCmZvciBlYWNoIHJvdyBleGVjdXRlIHBy b2NlZHVyZSB0cF9jdXN0b21lcl9hX2RlbCgpOwo= --=====================_15354859==_--