Обсуждение: Simple Trigger Error

Hi,
I am trying to create a simple trigger which inserts a couple of fields
into a table, when data in inserted into another table.

I have two tables, 'addressbook' and 'phonebook'. When I insert data
into the addressbook (whose fields are name, address1, address2,
address3, phonenum), I want it to add 'name' and 'phonenum' field values
into 'phonebook' table.

The following is the trigger i wrote for the above..

---- Begin of Code ---

DROP TRIGGER phonebook on addressbook;

CREATE OR REPLACE FUNCTION add_to_phonebook() RETURNS TRIGGER AS $phonebook$
    DECLARE
        new_name varchar;
        new_phone varchar;

    BEGIN
        IF(TG_OP='INSERT') THEN
            INSERT INTO phonebook(name,phonenum)
VALUES(NEW.name,NEW.phonenum);
        END IF;
        RETURN NEW;
    END;

$phonebook$ LANGUAGE plpgsql;

CREATE TRIGGER phonebook AFTER INSERT ON addressbook FOR EACH ROW
EXECUTE PROCEDURE add_to_phonebook();

----- End of code ---
I created the two tables as follows

'addressbook'
  id serial | name varchar(100) | address1 varchar(100) | address2
varchar(100) | address3 varchar(100) | phonenum varchar(15)

'phonebook'
  id serial | name varchar(100) | phonenum varchar(100)

Now, when I try to insert data into the addressbook and the trigger
runs, I get into an ever ending process accompanied by following in my
terminal where I run it:
""SQL statement "INSERT INTO phonebook(name,phonenum) VALUES( $1 , $2 )"
PL/pgSQL function "add_to_phonebook" line 7 at SQL statement""

When this happens, my processor runs at 100% and when I press Ctrl+C, it
continues for few seconds and I get my terminal gets filled with the
above stated message. Also that, the original 'insert into' operation
over the 'addressbook' table doesn't happen.

Where am I wrong in the trigger function ?

--
With Regards

Parthan (TechnoFreak)

.   A Proud GNU/Linux User and Ubuntero
.0.
..0 [Web] https://wiki.ubuntu.com/Parthan
000 [Blog]http://technofreakatchennai.wordpress.com

am  Wed, dem 20.12.2006, um  9:31:54 +0530 mailte Parthan folgendes:
> Hi,
> I am trying to create a simple trigger which inserts a couple of fields
> into a table, when data in inserted into another table.
>
> I have two tables, 'addressbook' and 'phonebook'. When I insert data
> into the addressbook (whose fields are name, address1, address2,
> address3, phonenum), I want it to add 'name' and 'phonenum' field values
> into 'phonebook' table.
>
> The following is the trigger i wrote for the above..
>
> ---- Begin of Code ---
>
> DROP TRIGGER phonebook on addressbook;
>
> CREATE OR REPLACE FUNCTION add_to_phonebook() RETURNS TRIGGER AS $phonebook$
>     DECLARE
>         new_name varchar;
>         new_phone varchar;
>
>     BEGIN
>         IF(TG_OP='INSERT') THEN
>             INSERT INTO phonebook(name,phonenum)
> VALUES(NEW.name,NEW.phonenum);
>         END IF;
>         RETURN NEW;
>     END;
>
> $phonebook$ LANGUAGE plpgsql;
>
> CREATE TRIGGER phonebook AFTER INSERT ON addressbook FOR EACH ROW
> EXECUTE PROCEDURE add_to_phonebook();
>
> ----- End of code ---

Sorry, i can't reproduce your problem:

test=# create table tele (name text, phone text);
CREATE TABLE
test=*# create table adr (id serial, name text, phone text, city text);
NOTICE:  CREATE TABLE will create implicit sequence "adr_id_seq" for serial column "adr.id"
CREATE TABLE
test=*# commit;
COMMIT
test=# create or replace function add_phone() returns trigger as $$ begin IF(TG_OP='INSERT') THEN insert into tele
(name,phone) values (new.name,new.phone);end if;return new; end; $$ language plpgsql; 
CREATE FUNCTION
test=*# commit;
COMMIT
test=# CREATE TRIGGER addphone after insert on adr for each row execute procedure add_phone();
CREATE TRIGGER
test=*# commit;
COMMIT
test=# insert into adr (name, phone,city) values ('ich', '123', 'Dresden');
INSERT 0 1
test=*# select * from tele;
 name | phone
------+-------
 ich  | 123
(1 row)


This is very similar to your example and it works.

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?
- 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 ;-)


I think, you should check your tables with \d addressbook and \d
phonebook to ensure that everything is okay.


Andreas
--
Andreas Kretschmer
Kontakt:  Heynitz: 035242/47215,   D1: 0160/7141639 (mehr: -> Header)
GnuPG-ID:   0x3FFF606C, privat 0x7F4584DA   http://wwwkeys.de.pgp.net