- starts by blocking inserts on a table,
- selects rows on the same table and validates that an INSERT does not violate business rules,
- performs the insert,
- updates another table for a specific row (this update only occurs on an insert), and
- commits the transaction.
Why do you want to block all inserts into a table when you are inserting another row into that table? This is a serious scalability problem and should only be used in very explicit situations and off the top of my head, I can't come up with a single application amongst the thousands of applications I've worked on that needed to lock an entire table from inserts.
As far as the code goes, I'll refer you to Books Online. This is nothing different than writing insert, update and select statements.
For More Information
- Dozens more answers to tough SQL Server questions from Michael Hotek are available here.
- The Best Microsoft SQL Server Web Links: tips, tutorials, scripts, and more.
- The Best SQL Web Links
- Have a SQL Server tip to offer your fellow DBAs and developers? The best tips submitted will receive a cool prize. Submit your tip today!
- Ask your technical SQL Server questions -- or help out your peers by answering them -- in our live discussion forums.
- Ask the Experts yourself: Our SQL, database design, Oracle, SQL Server, DB2, metadata, object-oriented and data warehousing gurus are waiting to answer your toughest questions.
This was first published in March 2003