Re: a trigger question

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.
>
> How can I solve this problem using trigger?

You either make the trigger fire AFTER the insert or you make
the constraint deferred.

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 #