Re: creating triggers: need help
| От | Bartosz Dmytrak |
|---|---|
| Тема | Re: creating triggers: need help |
| Дата | |
| Msg-id | CAD8_UcZY8jN4EeV6fatLRmJTDeMQOUnduOQJxOn=-VC8h304dQ@mail.gmail.com обсуждение исходный текст |
| Ответ на | creating triggers: need help (Navdeep Singh <navdeeps.iitd@gmail.com>) |
| Список | pgsql-novice |
Hi,
Regards,
Bartek
I'm sorry for delay (weekend),
Assumption:
1 - we use plpgsql;
2 - "name" field is primary key (to find proper row to be moved)
3 - we have got 2 tables:
CREATE TABLE public.people
(
name text NOT NULL,
house_no integer,
CONSTRAINT people_pkey PRIMARY KEY (name)
)
WITH (
OIDS=FALSE
);
and:
CREATE TABLE public.rich
(
name text NOT NULL,
house_no integer,
CONSTRAINT rich_pkey PRIMARY KEY (name)
)
WITH (
OIDS=FALSE
);
We have to:
1. Create trigger function, could be like this:
CREATE OR REPLACE FUNCTION public."trgRich"()
RETURNS trigger AS
$BODY$
BEGIN
WITH moved_row AS (
DELETE FROM public."people"
WHERE "name" = NEW."name"
RETURNING *
)
INSERT INTO public."rich"
SELECT * FROM moved_row;
RETURN NULL;
END;
$BODY$
LANGUAGE plpgsql VOLATILE SECURITY DEFINER;
ALTER FUNCTION public."trgRich"()
OWNER TO postgres;
2. Create trigger on table public.people - this trigger should use new trigger function, and should be conditional (house_no >=10). Trigger should be fired after update (update only for house_no field). In my example this is row level trigger
CREATE TRIGGER "becomeRich"
AFTER /*INSERT OR*/ UPDATE OF house_no
ON public.people
FOR EACH ROW
WHEN ((new.house_no >= 10))
EXECUTE PROCEDURE public."trgRich"();
more theory in documentation:
trigger -
trigger functions (procedures):
WITH:
I wonder why You don't want to fire trigger when record with more then 10 houses is inserted into "people" table - this will create data inconsistency. I put "INSERT OR" part of trigger definition in comment to give You the possibility to enable this "feature".
good luck.
Regards,
Bartek
2012/2/24 Navdeep Singh <navdeeps.iitd@gmail.com>
Hey,Yes, the rich person is to be removed from the table "persons". The rich table is a separate table.Also, I'm running psql version 9.0.5, server 9.1.2.Thanks and regards,NavdeepOn Fri, Feb 24, 2012 at 2:56 AM, Bartosz Dmytrak <bdmytrak@eranet.pl> wrote:Hi,few more questions...Should new rich person be removed from table "persons"?Does "rich" table inherits from "persons", or it is separate table?btw. which PostgreSQL version - that is important when answer for first question is YES?Regards,
Bartek2012/2/23 Navdeep Singh <navdeeps.iitd@gmail.com>Hey!I need to fire the trigger only when the number of houses is updated! Also, I'm considering only increment scenarios.Thanks and regards,NavdeepOn Thu, Feb 23, 2012 at 9:20 PM, Bartosz Dmytrak <bdmytrak@eranet.pl> wrote:Hi,what about scenario when reach person lost his houses?trigger should be fired only for new entries and/or updated?
Regards,
Bartek2012/2/23 Navdeep Singh <navdeeps.iitd@gmail.com>Hey!I am a penultimate year student of computer science and engg. I am looking for a way to create a trigger in postgresql. I've found some abstract methods for this on web but could not implement it. The description of the trigger is as follows:I have two tables, one named people and other named rich. The people table contains two columns: name, no. of houses. The rich table contains the same columns. I need to create a trigger to insert the tuple of the people table into the rich table when the number of houses of the given person (name) reaches, lets say 10.Please help me with the sql commands to implement the aforementioned transaction.Thanks,Navdeep
В списке pgsql-novice по дате отправления: