1

I'm switching from using a framework's model handling to using straight sql for the purpose of improving the performance. The framework would handle the model update and insert, and now I'm having a hard time with the validation aspect.

I'm updating a model with several relationships, the framework allows all queries to fail if one fails. I'm trying to emulate this with multiple queries.

How do I check validity of several tables before starting to write any data to the database for update and create functions? The added difficulty about the create queries is that the relationship tables need the id of the first one to be either created first, or validity of a key to be ignored on queries.

Daniel
  • 34,125
  • 17
  • 102
  • 150
  • 1
    Sounds like you should use transactions. This will allow you to rollback multiple insert/update/delete if anything fails. – George Mastros May 20 '14 at 16:53
  • ha - that's a concept I've now been familiar with for about a minute. It seems to be doing just that. – Daniel May 20 '14 at 16:55

1 Answers1

0

Normally you do this with a combination of constraints and transactions. Create PK, FK, and check constraints as needed. Possibly also create update timestamp triggers if you want to ensure you aren't overwriting a row someone else just modified. If an error is raised on the insert, update or delete, rollback the transaction (or handle it).

woot
  • 7,406
  • 2
  • 36
  • 55
  • how would I check whether there was an error in any of the queries and roll back a transaction? would I just use `try` and `catch` on the backend side? – Daniel May 20 '14 at 17:19
  • Typically, yes, a raise error on the database manifests as a raise on the front end as well. – woot May 20 '14 at 17:25
  • can I use it to detect which form input caused the issue? – Daniel May 20 '14 at 19:23
  • That I'm not sure of. The error message probably will have some information as to what column failed the check, but it may not have everything you are looking for. It isn't difficult to test it out. I would try it, but I don't have SQL Server installed right now. – woot May 20 '14 at 19:25
  • I've found this http://stackoverflow.com/questions/3935900/how-to-commit-and-rollback-transaction-in-sql-server, looks like I can use SQL to get custom info – Daniel May 20 '14 at 19:40
  • You can. I haven't used SQL Server in years, so I kind of forget all the ins and outs, but there are definitely some @@ variables you can access and wrap. If you need to do something more in-depth, you could consider using stored procedure. Good luck! – woot May 20 '14 at 20:48
  • I didn't mention that I'm using coldfusion, I'm seeing now that it has some functions that make this a bit easier. – Daniel May 20 '14 at 20:53