Re: a trigger question

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(at)megazone23(dot)bigpanda(dot)com]
Sent: Wednesday, June 05, 2002 8:54 AM
To: Zhou, Lixin
Cc: 'pgsql-general(at)postgresql(dot)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.

On Wed, 5 Jun 2002, Zhou, Lixin wrote:

> 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.

In the below, you seem to be getting an extra set of quote marks (what
it's inserting is the string 'v' (where the single quotes are in the
string). I removed the foreign key constraint to see what was being
inserted.

It worked for me on 7.2 when I just did the (new.v) insert with
the constraint. You might want to turn on query logging and such to
see what's going on.

> 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';

Zhou, Lixin wrote:
> 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.

1) Fire the trigger BEFORE INSERT does work
2) If you make the constraint deferred
3) And let the trigger return NEW instead of NULL;

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

It also works for all other types of keys.

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) initially deferred
);

create function init_second_tbl() returns opaque as '
declare
begin
insert into second_tbl (v) values (new.v);
-- 1. this is wrong!
-- insert into second_tbl (v) values (quote_literal(new.v));
return new;
end;
' language 'plpgsql';

create trigger t_init_second_tbl
before insert
on first_tbl
for each row
execute procedure init_second_tbl();

insert into first_tbl (v, s) values ('v', 's');

Works as expected with v7.2.1. So what did you do? Let me
guess, you specified the constraint DEFERRABLE and then
forgot to actually put it into deferred mode, right? Well,
specifying it INITIALLY DEFERRED does the trick.

Jan

--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me. #
#================================================== JanWieck(at)Yahoo(dot)com #