Received: from maia.hub.org (maia-4.hub.org [200.46.204.183]) by mail.postgresql.org (Postfix) with ESMTP id 207C66338E0 for ; Thu, 6 May 2010 17:59:57 -0300 (ADT) Received: from mail.postgresql.org ([200.46.204.86]) by maia.hub.org (mx1.hub.org [200.46.204.183]) (amavisd-maia, port 10024) with ESMTP id 33673-06 for ; Thu, 6 May 2010 20:59:48 +0000 (UTC) X-Greylist: from auto-whitelisted by SQLgrey-1.7.6 Received: from mail.magwerks.com (mail.magwerks.com [209.248.233.131]) by mail.postgresql.org (Postfix) with ESMTP id B72C76338A8 for ; Thu, 6 May 2010 17:59:48 -0300 (ADT) Received: from [192.168.1.36] by mail.magwerks.com (GMS 15.01.3664/RS2750.00.1b502c7c) with ESMTP id bmvneaaa for pgsql-sql@postgresql.org; Thu, 6 May 2010 16:59:45 -0400 Message-ID: <4BE32DA4.3040209@magwerks.com> Date: Thu, 06 May 2010 16:59:16 -0400 From: Justin Graf User-Agent: Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.9.1.9) Gecko/20100317 Lightning/1.0b1 Thunderbird/3.0.4 MIME-Version: 1.0 To: "Plugge, Joe R." , pgsql-sql@postgresql.org Subject: Re: Column Specific Update Trigger Routine References: =?utf-8?Q?_<4B54FDF3.4070601@gmx.net>_<92869e661001190627i4d42e3?= =?utf-8?Q?68nbb0e96bf5c54ca76@mail.gmail.com>__<4B55E22F.80409@gmx.net>?= =?utf-8?Q?_<65937bea1001192145u380846f3u30cc10d9d1784690@mail.gmail.com>__?= In-Reply-To: =?utf-8?Q?_?= Content-Type: multipart/alternative; boundary="------------020700030303020904020605" X-AntiSpam: Checked for restricted content by Gordano's AntiSpam Software X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=-0.01 tagged_above=-5 required=5 tests=BAYES_20=-0.001, HTML_MESSAGE=0.001, T_RP_MATCHES_RCVD=-0.01 X-Spam-Level: X-Archive-Number: 201005/26 X-Sequence-Number: 34074 This is a multi-part message in MIME format. --------------020700030303020904020605 Content-Type: text/plain; charset="utf-8" Content-Transfer-Encoding: 7bit On 5/6/2010 4:12 PM, Plugge, Joe R. wrote: > > I am trying to create a update trigger on a table that basically will > only fire when a specific column is updated. I am using version 8.4.3. > > My plan of attack was to always fire on any row update, and pass in > the OLD and NEW column that I want to check. > > CREATE TRIGGER check_lockout > > AFTER UPDATE ON acct_table > > FOR EACH ROW > > EXECUTE PROCEDURE > acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); > > This fails with : > > [postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb > > ERROR: syntax error at or near "OLD" > > LINE 4: EXECUTE PROCEDURE > acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password); > > What am I doing wrong? Or is there a better way to go about this? > You don't call the trigger procedure with the old and new as parameters new and old are automatically created for the function acct_unlock() CREATE TRIGGER check_lockout AFTER UPDATE ON acct_table FOR EACH ROW EXECUTE PROCEDURE acct_unlock(); Next the trigger function would look something like this create or replace function acct_unlock() returns trigger as $$ if (OLD.userid <> NEW.password) do something end if; $$ keep in mind the acct_unlock must be returns trigger Then return either NEW or the OLD record OLD if not changing the record or NEW if the updated values are to be stored in the table. All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored. CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately. Thank you. --------------020700030303020904020605 Content-Type: text/html; charset="utf-8" Content-Transfer-Encoding: 8bit On 5/6/2010 4:12 PM, Plugge, Joe R. wrote:

I am trying to create a update trigger on a table that basically will only fire when a specific column is updated.  I am using version 8.4.3.

 

My plan of attack was to always fire on any row update, and pass in the OLD and NEW column that I want to check.

 

CREATE TRIGGER check_lockout

    AFTER UPDATE ON acct_table

    FOR EACH ROW

    EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);

 

 

This fails with :

 

[postgres@linux1505 ~]$ cat check_lockout_trig.sql | psql testdb

ERROR:  syntax error at or near "OLD"

LINE 4:     EXECUTE PROCEDURE acct_unlock(OLD.userid,OLD.ownerid,OLD.password,NEW.password);

 

 

What am I doing wrong? Or is there a better way to go about this?

                                          

 

 

You don't call the trigger procedure with the old and new as parameters

new and old are automatically created for the function  acct_unlock() 

CREATE TRIGGER check_lockout

    AFTER UPDATE ON acct_table

    FOR EACH ROW

    EXECUTE PROCEDURE acct_unlock();


Next the trigger function would look something like this

create or replace function acct_unlock()
returns trigger as
$$

if  (OLD.userid <> NEW.password)
    do  something
end if;

$$


keep in mind the acct_unlock must be  returns trigger

Then return either NEW or the OLD record 
OLD if  not changing the record or NEW if  the updated values are to be stored in the table.



All legitimate Magwerks Corporation quotations are sent in a .PDF file attachment with a unique ID number generated by our proprietary quotation system. Quotations received via any other form of communication will not be honored.

CONFIDENTIALITY NOTICE: This e-mail, including attachments, may contain legally privileged, confidential or other information proprietary to Magwerks Corporation and is intended solely for the use of the individual to whom it addresses. If the reader of this e-mail is not the intended recipient or authorized agent, the reader is hereby notified that any unauthorized viewing, dissemination, distribution or copying of this e-mail is strictly prohibited. If you have received this e-mail in error, please notify the sender by replying to this message and destroy all occurrences of this e-mail immediately.
Thank you.
--------------020700030303020904020605--