Database Discussion Boards

View All Threads

First Prev Next

created tables not dropped in a transaction rollback

Danzy8320-Jan-13 16:13

Danzy83

20-Jan-13 16:13

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.

Re: created tables not dropped in a transaction rollback

Mycroft Holmes20-Jan-13 16:32

Mycroft Holmes

20-Jan-13 16:32

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

Re: created tables not dropped in a transaction rollback

Eddy Vluggen21-Jan-13 2:06

Eddy Vluggen

21-Jan-13 2:06

Similar to below? That would remove the created table (and does)

SQL

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 Suspicious | :suss:

If you can't read my code, try converting it here[^]
They hate us for our freedom![^]

Re: created tables not dropped in a transaction rollback

Shameel21-Jan-13 22:08

Shameel

21-Jan-13 22:08

In almost all database systems that I have worked with, DDL statements such as CREATE and DROP are not considered part of a transaction.

Re: created tables not dropped in a transaction rollback

jschell22-Jan-13 8:16

jschell

22-Jan-13 8:16

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.

Re: created tables not dropped in a transaction rollback

Shameel22-Jan-13 23:46

Shameel

22-Jan-13 23:46

Thanks for pointing out. I didn't know that unlike Oracle, DDL statements are transactional in SQL Server.

Last Visit: 31-Dec-99 18:00 Last Update: 25-Apr-24 1:52

Refresh

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.