Re: update in triggers

[Please don't post in HTML.]

On Wed, Jan 19, 2005 at 04:45:14PM +1100, Jamie Deppeler wrote:

> What i am trying to do is to update a field based on a sql query
> set notes='hello' is just being used as a test but i can not seem
> to make this simple update work

Do you want to modify a column in the row being inserted or updated,
or do you want to update other rows in a table?  If you want to
modify the row being updated, use a BEFORE trigger, assign a value
to NEW.column_name, and return NEW.  Example:

CREATE TABLE foo (
    id     serial PRIMARY KEY,
    name   text NOT NULL,
    notes  text
);

CREATE FUNCTION set_notes() RETURNS trigger AS '
BEGIN
    NEW.notes := ''hello'';
    RETURN NEW;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER footrig BEFORE INSERT OR UPDATE ON foo
  FOR EACH ROW EXECUTE PROCEDURE set_notes();

INSERT INTO foo (name) VALUES ('Jamie');
SELECT * FROM foo;
 id | name  | notes
----+-------+-------
  1 | Jamie | hello
(1 row)

--
Michael Fuhr
http://www.fuhr.org/~mfuhr/