Trigger problem

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

Re: Trigger problem

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

Re: Trigger problem

Well.. This tables are only an example... They are much more complex in
fact, so I can't simply put data in the B table. I need to do it in
"normal" way. But after puting something into table A the trigger fires
and removes the invalid record (rollback), so I can't put anything into
the tables. So if the trigger is correct how can I put something into
both tables without doing it in the body of the trigger.
:-)
Thanx for help

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

Re: Trigger problem

What do you need the trigger for?

On Jun 11, 2:32 pm, Wojto wrote:
> Well.. This tables are only an example... They are much more complex in
> fact, so I can't simply put data in the B table. I need to do it in
> "normal" way. But after puting something into table A the trigger fires
> and removes the invalid record (rollback), so I can't put anything into
> the tables. So if the trigger is correct how can I put something into
> both tables without doing it in the body of the trigger.
> :-)
> Thanx for help
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

Re: Trigger problem

I could be wrong but is your problem based on the fact that you want
to select all items from Table A and any matching records from Table
B?

If so then ignore the use of the trigger and then use this query:

SELECT
pk_A,
pk_B
FROM
A
LEFT JOIN B
ON A.pk_A = B.fk_B_A


On Jun 11, 2:32 pm, Wojto wrote:
> Well.. This tables are only an example... They are much more complex in
> fact, so I can't simply put data in the B table. I need to do it in
> "normal" way. But after puting something into table A the trigger fires
> and removes the invalid record (rollback), so I can't put anything into
> the tables. So if the trigger is correct how can I put something into
> both tables without doing it in the body of the trigger.
> :-)
> Thanx for help
>
>
>
> > 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.- Hide quoted text -
>
> - Show quoted text -

Re: Trigger problem

Well.. to be honest I need to solve this problem as academic exercise...
It is rather didactic than business problem. So I need to enforce the
relationship and it must be done with trigger. It will be checked by
trial of puting wrong data in the tables (rollback) and than correct
data (normal commit). But with my declaration I can't put anything in
the tables... The trigger is fired just after the insert while I need it
to be fired, lets say, on commit. So this is the "real" prblem :-)

Thanx and sorry for this mess :-)

Re: Trigger problem

So based on your description, anything at all can be put into pk_A.
However, in B you to make sure that anything put into B.fk_B_A must
already exist in A.pk_A

Based on your DDL you can remove the constraint on fk_B_A because
we're going to replace this functionality with a Trigger.

Since this is educational I will direct you to look at the INSTEAD OF
clause for triggers, and tell you that you need the trigger to be on
B, not A.

If you are still stuck, just post back.

Cheers,
Jason Lepack

On Jun 11, 2:55 pm, Wojto wrote:
> Well.. to be honest I need to solve this problem as academic exercise...
> It is rather didactic than business problem. So I need to enforce the
> relationship and it must be done with trigger. It will be checked by
> trial of puting wrong data in the tables (rollback) and than correct
> data (normal commit). But with my declaration I can't put anything in
> the tables... The trigger is fired just after the insert while I need it
> to be fired, lets say, on commit. So this is the "real" prblem :-)
>
> Thanx and sorry for this mess :-)

Re: Trigger problem

Wojto (jestem.wojtek@interia.pl) writes:
> 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:

If I understand this correctly, you (or rather your professor) want to
enforce that for each row in A, there is at least one row in B.

While this requirement makes sense in many cases - for instance "an
Order must have at least one line item", it is not enforceable in SQL
Server, since it would require commit-time triggers/constraints/assertions.
That is, you would need a mechanism to violate the rules while in
the transaction, but when you commit you must have sorted things out.

There a few possible ways around it, but none of them are palatable.
Once is to have mutual constraints, and then use ALTER TABLE to
disable the constraints you temporary need to violate, and enable the
constraint at the end again. But since this would recheck the entire
table, this is not a viable option. And in any case, you should use
metadata operations in application code.

A slightly better option is to have a view that covers both tables,
and that only give permissions on that view. The view would have an
instead of trigger that inserts data into the right places.

And, of course, rather than a view, a stored procedure could do.


--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx