Posted by Jason Lepack on 06/11/07 18:12

So let's get this straight.

The constraint on fk_B_A will not let you enter data into that field
unless it already exists in pk_A in A.

Your trigger that you have created will ensure that no data will get
into pk_a that does not already exist in fk_B_A, so it seems to be
working fine.

What exactly do you want to do? Do you want to ensure that after you
insert a record into A, a matching record gets inserted into B?

If so then your trigger should insert a record if one didn't exist.

Cheers,
Jason Lepack

On Jun 11, 1:57 pm, Wojto <jestem.woj...@interia.pl> wrote:
> Hello!
> Another day, another problem... :-)
>
> I've got something like this:
>
> CREATE TABLE A (
> pk_A INT CONSTRAINT primarykey_A PRIMARY KEY
> );
>
> CREATE TABLE B (
> pk_B INT CONSTRAINT primarykey_B PRIMARY KEY,
> fk_B_A INT CONSTRAINT foreginkey_B REFERENCES A
> );
>
> and I want to force connection 1 to 1..n between tables A and B.
> Inserting into B and than into A is impossible because of reference
> constraint in table B. So I insert into A and then into B. To enforce
> the relation (1-1..n) I'll probably need a trigger.
> I wrote it like this:
>
> GO
> CREATE TRIGGER trig
> ON A
> AFTER INSERT
> AS
> BEGIN
> IF (SELECT COUNT(*) FROM A WHERE pk_A
> NOT IN ( SELECT fk_B_A FROM B)) > 0
> BEGIN
> ROLLBACK
> PRINT 'STOP!'
> END
> END
>
> but now I can't put any data in the tables. Do You know how should I
> write the trigger? Maybe there is a better solution?
> Regards,
> Wojtek
>
> P.S.
> I thought about DISABLE/ENABLE TRIGGER but i couldn't find how to
> enforce check before triggering-event appears...

[Back to original message]