Skip site navigation (1) Skip section navigation (2)

Re: creating triggers: need help

From: Bartosz Dmytrak <bdmytrak(at)eranet(dot)pl>
To: Navdeep Singh <navdeeps(dot)iitd(at)gmail(dot)com>
Cc: pgsql-novice(at)postgresql(dot)org
Subject: Re: creating triggers: need help
Date: 2012-02-26 22:29:38
Message-ID: CAD8_UcZY8jN4EeV6fatLRmJTDeMQOUnduOQJxOn=-VC8h304dQ@mail.gmail.com (view raw or flat)
Thread:
Lists: pgsql-novice
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
>>>>>
>>>>
>>>>
>>>
>>
>

In response to

pgsql-novice by date

Next:From: peterDate: 2012-02-27 09:56:13
Subject: Postgresql wont install
Previous:From: Tom LaneDate: 2012-02-24 22:20:27
Subject: Re: postgres will not start up - corrupted on restart

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group