Re: Simple Trigger Error

["Parthan SR" <python.technofreak@xxxxxxxxx>]

On 12/20/06, A. Kretschmer <andreas.kretschmer@xxxxxxxxxxxxxx> 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