Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 123»»»

DDL trigger Expand / Collapse
Author
Message
Posted Wednesday, May 26, 2010 9:37 PM
Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Friday, May 9, 2014 12:47 AM
Points: 3,448, Visits: 4,407
Comments posted to this topic are about the item DDL trigger
Post #928715
Posted Wednesday, May 26, 2010 11:26 PM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Monday, July 21, 2014 3:43 AM
Points: 1,938, Visits: 1,162
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
Posted Thursday, May 27, 2010 12:02 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Today @ 5:39 AM
Points: 3,966, Visits: 5,206
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
Posted Thursday, May 27, 2010 12:04 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Today @ 1:20 AM
Points: 1,374, Visits: 1,315
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
Posted Thursday, May 27, 2010 12:32 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 12,953, Visits: 10,722
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
Posted Thursday, May 27, 2010 12:47 AM


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Thursday, September 18, 2014 1:36 PM
Points: 6,002, Visits: 8,267
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
Posted Thursday, May 27, 2010 1:05 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 6:34 AM
Points: 12,953, Visits: 10,722
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
Posted Thursday, May 27, 2010 1:20 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, September 2, 2014 10:39 AM
Points: 1,194, Visits: 787
Good Question vk-kirov

i learned somthing about IMPLICIT_TRANSACTIONS
Post #928774
Posted Thursday, May 27, 2010 2:45 AM


SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Tuesday, June 25, 2013 12:33 PM
Points: 45, Visits: 211
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
Posted Thursday, May 27, 2010 3:33 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Monday, June 30, 2014 2:51 AM
Points: 2,036, Visits: 369
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 »

Add to briefcase 123»»»

Permissions Expand / Collapse