[NOVICE] Trigger unhappy (Correction)

Previous Topic Next Topic
 
classic Classic list List threaded Threaded
3 messages Options
Reply | Threaded
Open this post in threaded view
|

[NOVICE] Trigger unhappy (Correction)

Bendik Rognlien Johansen
Forgot to remove "OR" here IF NEW.indexed != true

Hello,
I have the following trigger on the table "records":

CREATE OR REPLACE FUNCTION records_update_trigger() RETURNS TRIGGER AS '
   BEGIN
     NEW.updated = now();
     IF OLD.address1 != NEW.address1 OR OLD.postalcode1 !=  
NEW.postalcode1
     THEN
       RAISE NOTICE ''Address was altered, position set to null'';
       NEW.position := NULL;
     END IF;


     IF NEW.indexed != true
     THEN
       NEW.indexed := false;
       RAISE NOTICE ''Index not set, unsetting indexed'';
     END IF;
     RETURN NEW;
   END;
' language plpgsql;

DROP TRIGGER update_trigger ON records;
CREATE TRIGGER update_trigger BEFORE UPDATE ON records FOR EACH ROW  
EXECUTE PROCEDURE records_update_trigger();



The "indexed" column on the table is default false.
Every time i do an update, the variable NEW.indexed is true, even  
when it is not set in the sql. The result is that indexed is never  
set to false in the second if-clause of the function.

What is causing this?


Thanks






---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

               http://www.postgresql.org/docs/faq
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] Trigger unhappy (Correction)

Miguel Beltran R.
you have
NEW.updated = now();

and
NEW.indexed := false;

see "=" != ":=". maybe is the problem


2005/5/24, Bendik Rognlien Johansen <[hidden email]>:

> Forgot to remove "OR" here IF NEW.indexed != true
>
> Hello,
> I have the following trigger on the table "records":
>
> CREATE OR REPLACE FUNCTION records_update_trigger() RETURNS TRIGGER AS '
>    BEGIN
>      NEW.updated = now();
>      IF OLD.address1 != NEW.address1 OR OLD.postalcode1 !=
> NEW.postalcode1
>      THEN
>        RAISE NOTICE ''Address was altered, position set to null'';
>        NEW.position := NULL;
>      END IF;
>
>      IF NEW.indexed != true
>      THEN
>        NEW.indexed := false;
>        RAISE NOTICE ''Index not set, unsetting indexed'';
>      END IF;
>      RETURN NEW;
>    END;
> ' language plpgsql;
>
> DROP TRIGGER update_trigger ON records;
> CREATE TRIGGER update_trigger BEFORE UPDATE ON records FOR EACH ROW
> EXECUTE PROCEDURE records_update_trigger();
>
> The "indexed" column on the table is default false.
> Every time i do an update, the variable NEW.indexed is true, even
> when it is not set in the sql. The result is that indexed is never
> set to false in the second if-clause of the function.
>
> What is causing this?
>
> Thanks
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
>                http://www.postgresql.org/docs/faq
>

---------------------------(end of broadcast)---------------------------
TIP 8: explain analyze is your friend
Reply | Threaded
Open this post in threaded view
|

Re: [NOVICE] Trigger unhappy (Correction)

Jeff Eckermann
In reply to this post by Bendik Rognlien Johansen

--- Miguel que te importa <[hidden email]> wrote:
> you have
> NEW.updated = now();
>
> and
> NEW.indexed := false;
>
> see "=" != ":=". maybe is the problem

Probably not.  But using "=" to mean ":=", and "!=" to
mean "<>", could be seen as bad style, even if those
happen to work here.

>
>
> 2005/5/24, Bendik Rognlien Johansen
> <[hidden email]>:
> > Forgot to remove "OR" here IF NEW.indexed != true
> >
> > Hello,
> > I have the following trigger on the table
> "records":
> >
> > CREATE OR REPLACE FUNCTION
> records_update_trigger() RETURNS TRIGGER AS '
> >    BEGIN
> >      NEW.updated = now();
> >      IF OLD.address1 != NEW.address1 OR
> OLD.postalcode1 !=
> > NEW.postalcode1
> >      THEN
> >        RAISE NOTICE ''Address was altered,
> position set to null'';
> >        NEW.position := NULL;
> >      END IF;
> >
> >      IF NEW.indexed != true
> >      THEN
> >        NEW.indexed := false;
> >        RAISE NOTICE ''Index not set, unsetting
> indexed'';
> >      END IF;
> >      RETURN NEW;
> >    END;
> > ' language plpgsql;
> >
> > DROP TRIGGER update_trigger ON records;
> > CREATE TRIGGER update_trigger BEFORE UPDATE ON
> records FOR EACH ROW
> > EXECUTE PROCEDURE records_update_trigger();
> >
> > The "indexed" column on the table is default
> false.
> > Every time i do an update, the variable
> NEW.indexed is true, even

Do you know this for a fact, or are you inferring it?
In any case, why bother with the test at all, seeing
that the "indexed" value will always be set to false
anyway, if it has not been explicitly set to true?

In case you have only shown a partial example, then
try testing for null: "IF NEW.indexed IS NULL".

> > when it is not set in the sql. The result is that
> indexed is never
> > set to false in the second if-clause of the
> function.
> >
> > What is causing this?
> >
> > Thanks
> >
> > ---------------------------(end of
> broadcast)---------------------------
> > TIP 5: Have you checked our extensive FAQ?
> >
> >                http://www.postgresql.org/docs/faq
> >
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend
>


               
__________________________________
Do you Yahoo!?
Yahoo! Small Business - Try our new Resources site
http://smallbusiness.yahoo.com/resources/

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to [hidden email])