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@Yahoo.com #