Thread: Trigger unhappy (Correction)

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

you have
NEW.updated = now();

and
NEW.indexed := false;

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


2005/5/24, Bendik Rognlien Johansen <bendik.johansen@gmail.com>:
> 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
>

--- Miguel que te importa <yourpadre@gmail.com> 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
> <bendik.johansen@gmail.com>:
> > 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/