<a href="http://g.adspeed.net/ad.php?do=clk&zid=15220&wd=468&ht=60&pair=as" target="_blank"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15220&wd=468&ht=60&pair=as" alt="i" width="468" height="60"/></a>
Log in
::
Register
::
Not logged in
Home
Tags
Articles
Editorials
Stairways
Forums
Scripts
Videos
Blogs
QotD
Books
Ask SSC
SQL Jobs
Training
Authors
About us
Contact us
Newsletters
Write for us
<a href="http://g.adspeed.net/ad.php?do=clk&zid=15491&wd=120&ht=300&pair=as" target="_top"><img style="border:0px;" src="http://g.adspeed.net/ad.php?do=img&zid=15491&wd=120&ht=300&pair=as" alt="i" width="120" height="300"/></a>
Recent Posts
Recent Posts
Popular Topics
Popular Topics
Home
Search
Members
Calendar
Who's On
Home
»
Article Discussions
»
Article Discussions by Author
»
Discuss content posted by vk-kirov
»
DDL trigger
27 posts, Page 1 of 3
1
2
3
»
»»
DDL trigger
Rate Topic
Display Mode
Topic Options
Author
Message
vk-kirov
vk-kirov
Posted Wednesday, May 26, 2010 9:37 PM
Hall of Fame
Group: General Forum Members
Last Login: Thursday, June 13, 2013 8:50 PM
Points: 3,208,
Visits: 4,178
Comments posted to this topic are about the item
DDL trigger
Post #928715
malleswarareddy_m
malleswarareddy_m
Posted Wednesday, May 26, 2010 11:26 PM
SSCommitted
Group: General Forum Members
Last Login: Today @ 4:08 AM
Points: 1,868,
Visits: 1,043
good question.
when SET IMPLICIT_TRANSACTIONS off it will shows two times and other wise if we put SET IMPLICIT_TRANSACTIONS on then second time it shows "invalid object".This is due to SET IMPLICIT_TRANSACTIONS off.
good explanation.
Malleswarareddy
I.T.Analyst
MCITP(70-451)
Post #928738
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
Posted Thursday, May 27, 2010 12:02 AM
Hall of Fame
Group: General Forum Members
Last Login: Today @ 3:51 AM
Points: 3,163,
Visits: 4,342
Good question,
emphasises the neccessity of impact analysis and the way SQL implements implicit transactions.
____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Post #928750
honza.mf
honza.mf
Posted Thursday, May 27, 2010 12:04 AM
Ten Centuries
Group: General Forum Members
Last Login: Today @ 4:19 AM
Points: 1,114,
Visits: 1,210
Great question.
The first part with deffered table name was easy. I missed the second part - the table is not dropped due to transaction.
See, understand, learn, try, use efficient
© Dr.Plch
Post #928751
Koen Verbeeck
Koen Verbeeck
Posted Thursday, May 27, 2010 12:32 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410,
Visits: 6,495
I do not fully understand.
The batch begins with:
SET IMPLICIT_TRANSACTIONS OFF
According to BOL, this means the following:
... SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.
However, the explanation says the following:
When the DROP TABLE statement is executed, an implicit transaction occurs. The DROP statement and the DDL trigger are run within that transaction.
What am I missing here? Is the DROP TABLE statement and the trigger regarded as an individual statement in autocommit mode?
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #928761
Hugo Kornelis
Hugo Kornelis
Posted Thursday, May 27, 2010 12:47 AM
SSCertifiable
Group: General Forum Members
Last Login: Today @ 4:43 AM
Points: 5,294,
Visits: 7,233
da-zero (5/27/2010)
I do not fully understand.
The batch begins with:
SET IMPLICIT_TRANSACTIONS OFF
According to BOL, this means the following:
... SET IMPLICIT_TRANSACTIONS OFF statement, which returns the connection to autocommit mode. In autocommit mode, all individual statements are committed if they complete successfully.
However, the explanation says the following:
When the DROP TABLE statement is executed, an implicit transaction occurs. The DROP statement and the DDL trigger are run within that transaction.
What am I missing here? Is the DROP TABLE statement and the trigger regarded as an individual statement in autocommit mode?
Note that IMPLICIT_TRANSACTION OFF is the default; vk-kirov could have omitted this and mentioned "default settings" in the text for the same effect.
Regardless of this setting, every statement that modifies anything in the DB is always in a transaction. If no transaction is active at the start of a statement, SQL Server will implicitly start a transaction. The IMPLICIT_TRANSACTION setting governs what happens after the statement finishes - OFF means auto-commit (i.e., the transaction that was started implicitly will end implicitly after the statement finishes), ON means manual commit (i.e. even if the transaction was implicitly started, it will remain open until either an explicit rollback or commit, or a rollback caused by error).
If you change the QotD to IMPLICIT_TRANSACTIONS ON (don't forget to drop the database trigger and then the table first), the second SELECT will return an error. Not because this setting causes the DROP TABLE to suddenly fail - there still is an error in the trigger, that will still cause the transaction to be rolled back. Only now, this is the transaction that was implicitly started by the first modifying statement (the CREATE TRIGGER statement), which was not yet committed. The DROP TABLE still fails, but because the CREATE TABLE is rolled back as well, the effect is that the table is gone.
@vk-kirov: Great question!
Hugo Kornelis, SQL Server MVP
Visit my SQL Server blog:
http://sqlblog.com/blogs/hugo_kornelis
Post #928765
Koen Verbeeck
Koen Verbeeck
Posted Thursday, May 27, 2010 1:05 AM
SSCrazy Eights
Group: General Forum Members
Last Login: Yesterday @ 6:30 AM
Points: 9,410,
Visits: 6,495
Allright Hugo, thanks for the explanation!
How to post forum questions.
Need an answer? No, you need a question.
What’s the deal with Excel & SSIS?
Member of
LinkedIn
. My blog at
LessThanDot
.
MCSA SQL Server 2012 - MCSE Business Intelligence
Post #928768
sharath.chalamgari
sharath.chalamgari
Posted Thursday, May 27, 2010 1:20 AM
Ten Centuries
Group: General Forum Members
Last Login: Yesterday @ 5:37 AM
Points: 1,054,
Visits: 687
Good Question vk-kirov
i learned somthing about IMPLICIT_TRANSACTIONS
Post #928774
Maddy...!
Maddy...!
Posted Thursday, May 27, 2010 2:45 AM
SSC Rookie
Group: General Forum Members
Last Login: Monday, June 04, 2012 4:33 AM
Points: 45,
Visits: 210
yah... really a good question and i got some info reading implicit transactions need 2 read jst think it to be the kick start
still u have this sort of question plz post my dear co db kings...........
Post #928812
ziangij
ziangij
Posted Thursday, May 27, 2010 3:33 AM
SSCommitted
Group: General Forum Members
Last Login: Friday, May 17, 2013 2:53 AM
Points: 1,552,
Visits: 359
thanks for this... i had a hard time cleaning this one
by mistake, I executed this in the UAT environment and then i began searching the syntax for dropping a trigger...
DROP TRIGGER triggy ON DATABASE;
Post #928838
« Prev Topic
|
Next Topic »
27 posts, Page 1 of 3
1
2
3
»
»»
Permissions
You
cannot
post new topics.
You
cannot
post topic replies.
You
cannot
post new polls.
You
cannot
post replies to polls.
You
cannot
edit your own topics.
You
cannot
delete your own topics.
You
cannot
edit other topics.
You
cannot
delete other topics.
You
cannot
edit your own posts.
You
cannot
edit other posts.
You
cannot
delete your own posts.
You
cannot
delete other posts.
You
cannot
post events.
You
cannot
edit your own events.
You
cannot
edit other events.
You
cannot
delete your own events.
You
cannot
delete other events.
You
cannot
send private messages.
You
cannot
send emails.
You
may
read topics.
You
cannot
rate topics.
You
cannot
vote within polls.
You
cannot
upload attachments.
You
may
download attachments.
You
cannot
post HTML code.
You
cannot
edit HTML code.
You
cannot
post IFCode.
You
cannot
post JavaScript.
You
cannot
post EmotIcons.
You
cannot
post or upload images.
Copyright © 2002-2013 Simple Talk Publishing. All Rights Reserved.
Privacy Policy.
Terms of Use.
Report Abuse.
