From: | "Parthan SR" <python(dot)technofreak(at)gmail(dot)com> |
---|---|
To: | pgsql-general(at)postgresql(dot)org |
Subject: | Re: Simple Trigger Error |
Date: | 2006-12-20 10:33:43 |
Message-ID: | 9d6d3deb0612200233s50e8937and5553a9f6e6fdad6@mail.gmail.com |
Views: | Raw Message | Whole Thread | Download mbox | Resend email |
Thread: | |
Lists: | pgsql-general |
On 12/20/06, A. Kretschmer <andreas(dot)kretschmer(at)schollglas(dot)com> wrote:
>
> But i have questions/suggestions:
> - you have never-used variables in your function. Perhaps you have an
> older version from the function with an error and the wrong version
> runs?
> - perhaps, you have an other trigger on phonebook that calls recursive
> the trigger on addressbook?
Thanks a lot for pointer. There was a 'phonebook' trigger set on phonebook
itself, which was resulting in a infinite loop.
test=# \d addressbook
Table "public.addressbook"
Column | Type |
Modifiers
----------+------------------------+----------------------------------------------------------
id | integer | not null default
nextval('addressbook_id_seq'::regclass)
name | character varying(100) |
address1 | character varying(100) |
address2 | character varying(100) |
address3 | character varying(100) |
phonenum | character varying(15) |
Indexes:
"addressbook_pkey" PRIMARY KEY, btree (id)
"addressbook_name_key" UNIQUE, btree (name)
Triggers:
phonebook AFTER INSERT ON addressbook FOR EACH ROW EXECUTE PROCEDURE
add_to_phonebook()
test=# \d phonebook
Table "public.phonebook"
Column | Type |
Modifiers
----------+------------------------+--------------------------------------------------------
id | integer | not null default
nextval('phonebook_id_seq'::regclass)
name | character varying(100) |
phonenum | character varying(15) |
Indexes:
"phonebook_pkey" PRIMARY KEY, btree (id)
Triggers:
phonebook AFTER INSERT ON phonebook FOR EACH ROW EXECUTE PROCEDURE
add_to_phonebook()
So, when i dropped this trigger and tried insert, it worked...
test=# DROP TRIGGER phonebook ON phonebook;
DROP TRIGGER
test=# INSERT INTO addressbook (name,address1,address2,address3,phonenum)
VALUES ('Andy','House 1','Second Street','Cochin','9898098980');
INSERT 0 1
test=# select * from phonebook;
id | name | phonenum
------+---------+------------
4025 | Andy | 9898098980
(1 row)
It works perfect, Thanks again for enlightening me.
- you can use a RULE instead a Trigger, an example:
>
> test=# create rule r_adr as on insert to adr do also insert into
> tele(name) values (new.name);
> CREATE RULE
> test=*# commit;
> COMMIT
> test=# insert into adr (name, phone,city) values ('du', '456',
> 'wilsdruff');
> INSERT 0 1
> test=*# select * from tele;
> name | phone
> ------+-------
> ich | 123
> du | 456
> du |
> (3 rows)
>
>
> Now i have a TRIGGER and a RULE, and both works ;-)
Ya, ok. Will try rule too. :)
I think, you should check your tables with \d addressbook and \d
> phonebook to ensure that everything is okay.
Thanks, I just did that :)
--
With Regards
---
Parthan.S.R.
Research Assistant
National Resource Center for Free/Open Source Software
Python Developer n00b
From | Date | Subject | |
---|---|---|---|
Next Message | Lars Heidieker | 2006-12-20 12:24:17 | Re: Stored Procedure and Trigger they puzzle me |
Previous Message | BigSmoke | 2006-12-20 10:19:47 | Re: permission in the db or in the application? |