Re: creating triggers: need help

Hi,
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 -
http://www.postgresql.org/docs/9.1/static/trigger-definition.html
http://www.postgresql.org/docs/9.1/static/sql-createtrigger.html

trigger functions (procedures):
http://www.postgresql.org/docs/9.1/static/plpgsql-trigger.html

WITH:
http://www.postgresql.org/docs/9.1/static/sql-select.html#SQL-WITH
http://www.postgresql.org/docs/9.1/static/queries-with.html

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(dot)iitd(at)gmail(dot)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,
> Navdeep
>
> On Fri, Feb 24, 2012 at 2:56 AM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)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,
>> Bartek
>>
>>
>> 2012/2/23 Navdeep Singh <navdeeps(dot)iitd(at)gmail(dot)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,
>>> Navdeep
>>>
>>> On Thu, Feb 23, 2012 at 9:20 PM, Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>wrote:
>>>
>>>> Hi,
>>>> what about scenario when reach person lost his houses?
>>>> trigger should be fired only for new entries and/or updated?
>>>>
>>>> Regards,
>>>> Bartek
>>>>
>>>>
>>>>
>>>> 2012/2/23 Navdeep Singh <navdeeps(dot)iitd(at)gmail(dot)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
>>>>> navdeeps(dot)iitd(at)gmail(dot)com
>>>>>
>>>>
>>>>
>>>
>>
>