function/trigger problems

Поиск
Список
Период
Сортировка
Искать
От
Rory Campbell-Lange
Тема
function/trigger problems
Дата
Msg-id
20020618090209.GA5600@campbell-lange.net
Список
I am trying to update a timestamp value in a related table using
functions and triggers, but failing miserably! I'd be grateful for some
help.

I'm trying to update the tstamp value in a whenever an  insert is done
on b where b.a_id = a.id. So 
	INSERT INTO b (a_id) values (1)
should update the tstamp value in a using the trigger.
My function is failing at the 'WHERE' clause. 

-----------------------------------------------------------------------

CREATE TABLE "a" (
	"id" SERIAL,
	"tstamp" timestamp
);

CREATE TABLE "b" (
	"id" SERIAL,
	"a_id" integer,
	"tstamp" timestamp default current_timestamp.
);


CREATE FUNCTION "fnTU" () RETURNS opaque AS 'BEGIN
UPDATE a SET tstamp = new.tstamp;
WHERE new.a_id = id;
RETURN new;
END' LANGUAGE 'plpgsql';

CREATE TRIGGER "trgTU" AFTER INSERT ON "b"  FOR EACH ROW EXECUTE PROCEDURE "fnTU" ();

COPY "a" FROM stdin;
1	2002-06-18 09:47:44.734527+01
2	2002-06-18 09:47:44.734527+01

COPY "b" FROM stdin;
1	1	2002-06-18 09:47:44.734527+01

-- 
Rory Campbell-Lange 


В списке pgsql-novice по дате отправления
От: Rory Campbell-Lange
Дата:
Сообщение: function delete problems
От: Manfred Koizar
Дата:
Сообщение: Re: function delete problems
FAQ