Data Integrity and Trigger Questions

Question

Data Integrity and Trigger Questions

dba_user

Default port

Points: 1458
More actions
September 27, 2004 at 10:51 am

#60318

Hello!
We have three tables
1.Resources (ResourceId (PK), ResourceName)
2. Connectors(ConnectorId(Pk), ConnectorName)
3. Connections (ConnectorId(PK), ResourceId(PK), Sequence)
We want the following rules:
1. If a ResourceId is deleted from Resources, then the Connection is deleted in the Connection table for that REsourceId and the associated ConnectorId from the Connections table is deleted from the Connectors table.
2. We don't want to delete a connection if the connectorId exists in the Connectors table. (A connector must ALWAYS have two Connections)
Would it be best to have Referential Integrity defined on the tables and delete the associated Connection when the ResourceId is deleted then have a trigger on the Resources table to delete the appropriate ConnectorId from Connectors table where the associated connectorId was deleted in the Connections table??
Is there a way to have a trigger on the connections table and not allow deletion inf the associated connectorid exists in the connector table? Like an Instead of Trigger??
Should I not even use Referential Integrity and enforce data integrity through triggers?
Thanks!!

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

There are several aproaches to do what you want.

What I usually do in these cases is to disallow DML directly to tables and perform the logic eather on specially created stored procedures or in the middle tier if the logic is very complex. For your case an sp seems adecuate to me.

I don't understand very well your second requirement:

"We don't want to delete a connection if the connectorId exists in the Connectors table."

Does that means that you can have a connection whithout a resourceID ? (I am assuming you deleted it, right?&nbsp

HTH

* Noel

dba_user Default port Points: 1458 More actions · Answer 2

Thanks for the reply!

I think the main issue with the connection is that a Connector must always have two connections.

So, if I didn't set up Referential Integrity on the tables and created a stored procedure to do the DML, how would that get executed??

Thanks again!

noeld SSC Guru Points: 96590 More actions · Answer 3

I am not saying that you remove referential integrity. This could very well be a design issue

ex:

1.Resources (ResourceId (PK), ResourceName)

2.Connectors(ConnectorId(Pk), ConnectorName)

3.Connections (ConnectorId1(PK), ConnectorId2(PK), ResourceID(PK))

I really don't know enough about your model but You should look at it more than once to see if a different approach seems natural

On the other hand The Logic once encapsulated on the sp means that when you want to delete an item it will be just a matter of calling the sp. Just create the three (Insert,Update and Delete) and grant access accordingly

HTH

* Noel