1

Not even sure how to search for something like this, but for example:

First Table:
Id, Date, Maximum records
Second Table:
Id, Date, more properties

Is it possible to define some rules on the server side, if the second Table has too many records with the same date and it exceeds the maximum records value for the current date from the First Table then the server would throw back an error or some kinda exception that would prevent an sql insert query for that date?

Possible:

First Table:
1,Date1,2
Second Table:(Maximum 2 on Date 1)
1,Date3,...
2,Date1,...
3,Date1,...

Not possible:

First Table:
1,Date1,2
Second Table:(Maximum 2 on Date 1)
1,Date3,...
2,Date1,...
3,Date1,...
4,Date1,...

If it is not possible to do this then what would be the best way to achieve this type of checking on an windows forms application with entity framework?

M.J.
  • 71
  • 2
  • 10
  • You could perhaps use a `Trigger` to reject the insert. [This question](http://stackoverflow.com/questions/2538786/how-to-abort-insert-operation-in-mysql-trigger/8559284#8559284) has a possible solution for MySQL. Something similar might work for SQL-Server –  Oct 21 '13 at 21:16
  • This seems like a odd requirement for a database design. What are you doing that causes you to have this requirement? – Scott Chamberlain Oct 21 '13 at 21:38
  • @ScottChamberlain I would have many running applications at a time, which would check the current database table. In a case when someone from many of those applications would want to insert a record in the last remaining spot(specific date) i would want the server to validate that not the client application validation, since there might be timeouts and so on. – M.J. Oct 23 '13 at 09:46
  • @M.J. That is a common problem that is easily solvable that does not require using separate tables at all. All that can be done with Transactions and Stored Procedures on the server. Don't fall in to a [XY Problem](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem) I suggest asking a new question and explain what you are trying to do and the challenges you face (like having the server validate instead of the client) and ask the question "*How do I solve this problem?*" not "*How do I implement this solution (to a problem I have not told you about)?*" – Scott Chamberlain Oct 23 '13 at 13:39
  • @ScottChamberlain Ok thanks for the explanation and i could have probably created that question not this question. :) – M.J. Oct 23 '13 at 18:13

1 Answers1

2

The short answer is yes.

You need to write a trigger (a special type of stored procedure that is executed as part of insert, delete and update statement execution) to enforce your constraints as they can't be expressed in a relational schema. The drawback is that unlike some SQL implementations, SQL Server triggers fire after all the work of the insert, delete or update statement has been done: if the trigger validation fails, the transaction is rolled back, meaning that potentially a lot of work must be undone.

Nicholas Carey
  • 71,308
  • 16
  • 93
  • 135