Thread: Trigger once again

I have created the following function:

CREATE OR REPLACE FUNCTION validate_tag_number()
    RETURNS OPAQUE AS '
    BEGIN
    IF new.tag_number=old.tag_number THEN
    RAISE EXCEPTION ''Number is already present '';
    END IF;
    RETURN NEW;
    END;
    'LANGUAGE 'plpgsql';

 I used it to create a trigger as follows;

    CREATE TRIGGER  validate_tag_number BEFORE UPDATE ON tag_vendor FOR EACH ROW
    EXECUTE PROCEDURE validate_tag_number();

The trigger seems to work to well. If I update values other than the
tag_number I get the 'Number is already present' message. I am somewhat
confused as their is no new.tag_number value being passed to the function.
Can anyone set me straight?

                       Thank you
                       Adrain Klaver

On Thu, 2002-11-28 at 21:55, Adrian Klaver wrote:
> I have created the following function:
...
>     IF new.tag_number=old.tag_number THEN
>     RAISE EXCEPTION ''Number is already present '';
...
> The trigger seems to work to well. If I update values other than the
> tag_number I get the 'Number is already present' message. I am somewhat
> confused as their is no new.tag_number value being passed to the function.
> Can anyone set me straight?

NEW contains the whole changed record; OLD contains the record before
the change.  If you "update values other than the tag_number", the
tag_number is not changed, therefore the condition is true.

--
Oliver Elphick                                Oliver.Elphick@lfix.co.uk
Isle of Wight, UK                             http://www.lfix.co.uk/oliver
GPG: 1024D/3E1D0C1C: CA12 09E0 E8D5 8870 5839  932A 614D 4C34 3E1D 0C1C
                 ========================================
     "The earth is the LORD'S, and the fullness thereof; the
      world, and they that dwell therein."       Psalms 24:1

On Thu, 28 Nov 2002, Adrian Klaver wrote:

> The trigger seems to work to well. If I update values other than the
> tag_number I get the 'Number is already present' message. I am somewhat
> confused as their is no new.tag_number value being passed to the function.
> Can anyone set me straight?

Try it by validating 2 columns, that should do the trick:

 CREATE OR REPLACE FUNCTION validate_tag_number()
       RETURNS OPAQUE AS '
     BEGIN
       IF new.tag_number=old.tag_number and new.other_column=old.other_column THEN
        RAISE EXCEPTION ''Tag is already present '';
       END IF;
        RETURN NEW;
     END;
       'LANGUAGE 'plpgsql';

If that doesn't work then send me the schema of your table and I will work
on it.

Tariq Muhammad
Liberty RMS
tariq@libertyrms.info
v:416-646-3304 x 111
c:416-993-1859
p:416-381-1457

Adrian Klaver <aklaver@attbi.com> writes:
> I have created the following function:

> CREATE OR REPLACE FUNCTION validate_tag_number()
>     RETURNS OPAQUE AS '
>     BEGIN
>     IF new.tag_number=old.tag_number THEN
>     RAISE EXCEPTION ''Number is already present '';
>     END IF;
>     RETURN NEW;
>     END;
>     'LANGUAGE 'plpgsql';

>  I used it to create a trigger as follows;

>     CREATE TRIGGER  validate_tag_number BEFORE UPDATE ON tag_vendor FOR EACH ROW
>     EXECUTE PROCEDURE validate_tag_number();

> The trigger seems to work to well. If I update values other than the
> tag_number I get the 'Number is already present' message. I am somewhat
> confused as their is no new.tag_number value being passed to the function.
> Can anyone set me straight?

NEW and OLD are record variables holding the whole row being updated
(new and old versions).  The above trigger strikes me as pretty
pointless, since as you've found out it will object to perfectly
reasonable updates.  What is it you really want to do --- ensure there's
only one occurrence of a given tag_number in the table?  If so, you
need a unique index on the column, not a trigger.

            regards, tom lane