Two-way cascade of SQL Server?

Question

I am trying to build a database in SQL Server Management Studio 2008 that will manage data without the need for software data processing, but I have some problems at the moment.

I have a main table called person, this table contains basic information about the person, and this is related to the second table, called the client, in which they are included if they want to have permission to the transaction. After the transaction, the customer ID is placed in the transaction table.

|Person| |Customer| |Transaction| |------| |--------| |-----------| | ID |-\ | ID |-\ | ID | | | \| person | \| Customer | | | | | | | 

Of the two links, the identifier is PK, and the secondary elements (person / client) are FK. The problem I ran into is requiring the person to be contained if they have completed a transaction, and therefore their client ID appears in the transaction table.

I am not 100% sure about what approach to take, will the “Delete rule - cascade” between the human client with the “Delete rule - no action” between the “Client transaction” create the desired effect?

Any thought approaches or a better approach would be greatly appreciated on this.

Answer 1

I think yes,

 ON DELETE CASCADE 

in Customer.Person foreign key and

 ON DELETE NO ACTION 

in Transaction.Customer foreign key will have the desired effect.

Then, any attemp to remove Person will remove the associated Customer string, unless there are related Transcations. This will cause an error, and DELETE will roll back.

Answer 2

You can only have a deletion trigger in the person table that stops deletion if the remote user has entries in the transaction table.