Posted by Wojto on 06/11/07 17:57

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]