Is Insert, Delete, Update already have transaction
-
Is Insert, Delete, Update already have transaction inside if yes how it help us and for what it ?? To study, study and only to study
_J_ wrote: Is Insert, Delete, Update already had transaction inside I'm not entirely sure what you are asking, however I am guessing you are asking: Are INSERT, DELETE and UPDATE statements wrapped in a transaction? Yes, and the SELECT statement too unless you wrap a sequence of these statements in a transaction. SQL Server will always wrap indiivdual statements in a transaction so that the statement can complete successfully and does not interfer with other queries or so other queries do not interfere with it. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
_J_ wrote: Is Insert, Delete, Update already had transaction inside I'm not entirely sure what you are asking, however I am guessing you are asking: Are INSERT, DELETE and UPDATE statements wrapped in a transaction? Yes, and the SELECT statement too unless you wrap a sequence of these statements in a transaction. SQL Server will always wrap indiivdual statements in a transaction so that the statement can complete successfully and does not interfer with other queries or so other queries do not interfere with it. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
OK, thanks. So if i do deletion and 100% shure that more than one row will deleted should use a transaction as i now understood No. So i don't need to wrap it with my tranascation like : Begin TRAN DELETE ..... COMMIT To study, study and only to study
I'm not sure that you understood me. If you are doing one delete statement only then you don't need to explicitly wrap it in a transaction because SQL Server will do that for you. (NOTE: Other database products may vary) So
DELETE MyTable WHERE SomeColumn='SomeValue'does not have to be wrapped in a transaction. It can delete 0, 1 or many rows. However, if you need to delete from, say, two separate tables at the same time you might want to use a transaction, soBEGIN TRANSACTION
DELETE MyTable WHERE SomeColumn='SomeValue'
DELETE MyOtherTable WHERE SomeOtherColumn='SomeOtherValue'
COMMIT TRANSACTIONObviously, after each delete you may want to check for errors so that you can roll back a transaction if it fails. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
-
I'm not sure that you understood me. If you are doing one delete statement only then you don't need to explicitly wrap it in a transaction because SQL Server will do that for you. (NOTE: Other database products may vary) So
DELETE MyTable WHERE SomeColumn='SomeValue'does not have to be wrapped in a transaction. It can delete 0, 1 or many rows. However, if you need to delete from, say, two separate tables at the same time you might want to use a transaction, soBEGIN TRANSACTION
DELETE MyTable WHERE SomeColumn='SomeValue'
DELETE MyOtherTable WHERE SomeOtherColumn='SomeOtherValue'
COMMIT TRANSACTIONObviously, after each delete you may want to check for errors so that you can roll back a transaction if it fails. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
Thank u, u r the best. Now everything clear me, Thanks in advance I have another question to about transaction. Transacttion in stoe proc , in the begining i save @@TRANCOUNT to local variable (@tranCount) after this i start transaction (BEGIN TRANSACTION), i do my work ... .. maybe call to other store procs, .. .. and now before COMMIT or ROLLBACK i must to check my local variable (@tranCount) with @@TRANCOUNT ?? ------------------------------------ To study, study and only to study
-
_J_ wrote: Is Insert, Delete, Update already had transaction inside I'm not entirely sure what you are asking, however I am guessing you are asking: Are INSERT, DELETE and UPDATE statements wrapped in a transaction? Yes, and the SELECT statement too unless you wrap a sequence of these statements in a transaction. SQL Server will always wrap indiivdual statements in a transaction so that the statement can complete successfully and does not interfer with other queries or so other queries do not interfere with it. Does this help?
My: Blog | Photos | Next SQL Presentation WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More
There are other potential transaction configurations, depending upon the database platform and the method of access. SQL Server runs, by default, in AutoCommit mode although it can be disabled, requiring explicit COMMIT or ROLLBACK calls. Oracle does not run in AutoCommit mode by default (in fact I have never determined if it can; I doubt it). Of course, neither of the above comments really matter if the developer is using a managed data provider, all of which (along with the underlying ODBC and OleDB layers) implement some form of AutoCommit heuristics as far as I know, forcing the behavior to act as you describe. I'm mainly making the distinction between exec'ing SQL at the command line versus through a managed provider, on different DB servers. Have a good weekend.
The most exciting phrase to hear in science, the one that heralds the most discoveries, is not 'Eureka!' ('I found it!') but 'That's funny...’