Re: a trigger question - Mailing list pgsql-general

Thank all who answered and helped!

Here is what I learnt so far:

1) Fire the trigger AFTER INSERT.  BEFORE INSERT won't work.
2) Deferrable does not seem to be important.

I've tested above, it does work as expected for PK/FKs that are integers.

(As Stephan pointed out, it should work.  And he's right, it works.)

However, my specific problem is still not solved.  My original SQL
actually has varchar(25) as PK/FK (in the original post, I used int as
example because I did not realize that would make a difference).  I made
a simple test and it does exactly reproduce my problem.  So, I post it
below.

The problem is actually, I guess, the SQL statement somehow does not
insert the correct value to the second table.  For example, it may not
quote the varchar string correctly.

I tried a few things as shown below, I did not have any luck.

Thank you for any further help!

Lixin Zhou


================== A PostgreSQL Session ========================

Script started on Wed Jun  5 13:46:16 2002
$ cat test.sql
create database test_trigger;

\c test_trigger

create table first_tbl(
    v varchar(20) not null default 'abc',
    s text,
    primary key(v));

create table second_tbl(
    v varchar(20) not null default 'abc',
    s text,
    primary key(v),
    foreign key(v) references first_tbl(v));

create function init_second_tbl() returns opaque as '
declare
begin
    insert into second_tbl(v) values(quote_literal(new.v));
    -- 1. this does not work
    -- insert into second_tbl(v) values(new.v);
    -- 2. this does not work
    -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v) ||
'')'';
    -- execute s;
    -- 3. this does not work
    -- s := ''insert into second_tbl(v) values('' || quote_literal(new.v) ||
'')'';
    -- perform s;
    return null;
end;
' language 'plpgsql';

create trigger t_init_second_tbl
    after insert
    on first_tbl
    for each row
    execute procedure init_second_tbl();
$ psql -p 5556 -f test.sql
CREATE DATABASE
You are now connected to database test_trigger.
psql:test.sql:8: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index 'first_tbl_pkey' for table 'first_tbl'
CREATE
psql:test.sql:14: NOTICE:  CREATE TABLE / PRIMARY KEY will create implicit
index 'second_tbl_pkey' for table 'second_tbl'
psql:test.sql:14: NOTICE:  CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
CREATE
CREATE
CREATE
$ psql -p 5556 test_trigger
Welcome to psql, the PostgreSQL interactive terminal.

Type:  \copyright for distribution terms
       \h for help with SQL commands
       \? for help on internal slash commands
       \g or terminate with semicolon to execute query
       \q to quit

test_trigger=> \dt
     List of relations
    Name    | Type  | Owner
------------+-------+-------
 first_tbl  | table | lzhou
 second_tbl | table | lzhou
(2 rows)

test_trigger=> \d first_tbl
                     Table "first_tbl"
 Attribute |         Type          |        Modifier
-----------+-----------------------+------------------------
 v         | character varying(20) | not null default 'abc'
 s         | text                  |
Index: first_tbl_pkey

test_trigger=> insert into first_tbl(v,s) values('v', 's');
ERROR:  <unnamed> referential integrity violation - key referenced from
second_tbl not found in first_tbl
test_trigger=> \q
$ exit
exit

Script done on Wed Jun  5 13:47:15 2002



-----Original Message-----
From: Stephan Szabo [mailto:sszabo@megazone23.bigpanda.com]
Sent: Wednesday, June 05, 2002 8:54 AM
To: Zhou, Lixin
Cc: 'pgsql-general@postgresql.org'
Subject: Re: [GENERAL] a trigger question


On Tue, 4 Jun 2002, Zhou, Lixin wrote:

> Give two tables A and B.  B has a field that references A's primary key.
>
> For example:
>
> create table A(
>     i int not null,
>     s text,
>     primary key(i));
>
> create table B(
>     i int not null,
>     s text,
>     primary key(i),
>     foreign key(i) references A(i));
>
> I like to create a trigger on table A.  When a new row is inserted into A
> (ex: with i = 5), I like to have the trigger inserts a new row in table B
> whose field "i" has the same value as that of the A's (ex: i = 5).
>
> As I do this, the error message is something like: "referential
integration
> violation - key referenced in B not found in A".  This makes sense to me
> since at the time the trigger inserts in B, A's new row is not visible yet
> -- not committed yet.

Actually, I'd think that should work since it should be post statement
that the constraint runs. Can you send the full info on the tables and
triggers you were using?

As a workaround, you could see if making the constraint deferrable and
initially deferred works.