created tables not dropped in a transaction rollback
-
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge. My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back. I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2). Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
-
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge. My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back. I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2). Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
Not something I have ever needed to do. However why is explicitly dropping the table and issue, you already trap the error and have a rollback simply add the test and drop in that trap.
Never underestimate the power of human stupidity RAH
-
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge. My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back. I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2). Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
Similar to below? That would remove the created table (and does)
BEGIN TRANSACTION
CREATE TABLE Test (Id BIGINT)
INSERT INTO Test (Id) VALUES (1)ROLLBACK
SELECT *
FROM information_schema.tables
WHERE TABLE_NAME = 'Test'Danzy83 wrote:
However, the tables created within the transaction are not dropped from the database. This is unusual to me.
It'd be erroneous. Have you changed the locking-options? FWIW, it'd probably be the wisest to use a temp-table, not a real one.
Bastard Programmer from Hell :suss: If you can't read my code, try converting it here[^] They hate us for our freedom![^]
-
Hello experts. I have noticed something that is unfamiliar to me in my database knowledge. My application creates tables and inserts initial values into them based on user specifications. More than two tables are created in the process. The table creation and inserts are done within a transaction. When an error occurs, the transaction is rolled back. I just noticed that when the transaction is rolled back, all inserts are erased as expected. However, the tables created within the transaction are not dropped from the database. This is unusual to me. I don't know if I am missing something or that is how it happens with SQL server (2008 R2). Is there any way the tables can be dropped without specifying them one at a time in code? Please help.
-
In almost all database systems that I have worked with, DDL statements such as CREATE and DROP are not considered part of a transaction.
-
Shameel wrote:
In almost all database systems that I have worked with, DDL statements such as CREATE and DROP are not considered part of a transaction.
Yes but it appears that it is part of TSQL which is what the poster asked about.