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/