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