Question re inserts and updates
-
Greetings, I am doing inserts and updates using the query analyzer, for example, UPDATE table_name set
; or insert into table_name ; This is not within a transaction block. When are inserts, updates and deletes committed? Must I do an explicit commit? WHen I tried it a message was returned indicating it was not part of a transaction block. THanks. -
There is a setting that determines when the transaction is commited: Set Implicit_transactions. If this is off, the transaction is committed immediately unless you use BEGIN TRANSACTION first. When I use Query Analyzer, my statements are automatically committed so it must be off by default.
If you want an explicit transaction and the setting is off,you will do this
Begin tran
---update, insert, delete
commit trans
If you have the setting on, you can use explicit transactions or do this
-- update, insert or delete
commit tran
There is a good example in SQL Books Online on this topic, be sure to check it out.
Aunt Kathi Data Platform MVP
Author of Expert T-SQL Window Functions
Simple-Talk Editor -
Many ppl on here (I've been too lazy to do it unless doing big bulk updates!) ALWAYS start their scripts with a BEGIN TRANSACTION when working in QA.
They also automatically put a ROLLBACK TRANSACTION at the bottom of their script. This allows them to test it out, etc and, once happy, they replace the ROLLBACK with a COMMIT. This has saved my bacon once or twice
Otherwise QA commits things there and then when you execute them.Beware that QA keeps a single connection open per window by default - the same connection (and thus the same transaction context) is reused between subsequent executions. This means that I could do something like
BEGIN TRANSACTION
delete from table A
and then execute this. Note that the transaction is still alive. When I quit QA or close that query window, I get a question about committing or rolling back existing transactions...
-
Thank you both.
-
The suggestion Ian gave you is fine in development environment, but be carefull with this in production with multiple users using your database. The Begin Transaction and running test SQL statements in QA without ROLLBACK or COMMIT are locking up the records and any users hitting the tables you are working on may get stuck...
I use this technique sometimes when I need to see what the impact of the statement is going to be without the need to restore the DB if I mess up. But this requires some follow up query to find out if the results are the one I expected before ROLLBACK or COMMIT. And in the meantime stuff is locked...
---------------------------------------------
[font="Verdana"]Nothing is impossible.
It is just a matter of time and money.[/font] -
Yeah - most certainly not to use in a busy production environment
Same goes for normal app programming - don't have user interactions in the middle of a DB transaction. Unless you are the DBA and using Query Analyser and don't give a stuff about the other users 
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply