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»»

Question of the Day for 17 Oct 2005 Expand / Collapse
Author
Message
Posted Tuesday, October 18, 2005 9:42 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 5:17 PM
Points: 2,418, Visits: 1,501

SSC ertifiable did you mean that within a single transaction if a major error occurs there is a rollback? I think the original statement may have been a bit too open.

 



Not all gray hairs are Dinosaurs!
Post #229792
Posted Tuesday, October 18, 2005 9:59 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Yes... try opening a transaction and executing something that will violate a constraint. The whole operation will be rolled back.
Post #229801
Posted Wednesday, October 19, 2005 1:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:43 AM
Points: 145, Visits: 10
Please read my post. According to the text of the question, we aren't running in a transaction. No transaction = no rollback.


Post #230014
Posted Wednesday, October 19, 2005 6:43 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
That's what you have to learn in that question ::


CREATE TABLE dbo.A (PK int not null primary key clustered)
GO
CREATE TRIGGER dbo.trDemoA_IUD ON dbo.A
FOR INSERT, UPDATE, DELETE
AS
SET NOCOUNT ON
ROLLBACK TRANSACTION
RAISERROR ('Cannot allow any DML operation on table A', 13, 1)
SET NOCOUNT OFF
GO
INSERT INTO A (PK) VALUES (4)
GO
DROP TABLE dbo.A
GO

This transaction will rollback even if I didn't create the tran myself.
Post #230079
Posted Wednesday, October 19, 2005 10:57 AM
SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, October 24, 2008 12:15 PM
Points: 999, Visits: 4

In regards to the discussion of pseduo coding this question and that fact that it might be a red hearing to give the information on no parent child relationship.

I belive that this is a great question, to those of us that have taken microsoft exams know, they are filed with questions worded such as these, they test you not only on your knowledge of the problem, but also on your ability to take the information given, discount what is not revelent to the issue and make your decision based on the facts given. Yes its a little hard to read and takes a minute to understand, but when faced with a issue in real life, no one is going to pseduo code the problem for you and take out what is not relevent.

The only answer of course is the correct answer given, complete rollback.

Also, Triggers are always run inside of a transaction by default, you do not have to explicity identifiy one.

my 2 cents




Post #230312
Posted Thursday, October 20, 2005 1:47 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:43 AM
Points: 145, Visits: 10

There is no transaction, according to the question. You are coming into the trigger without being in the context of a transaction. In other words, there was no "begin transaction" executed anywhere before the trigger started.

No transaction = no rollback.




Post #230524
Posted Thursday, October 20, 2005 6:56 AM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Wednesday, October 8, 2014 4:15 AM
Points: 20,581, Visits: 9,619
Dude run my code, learn something and stop asking for free points.
Post #230632
Posted Monday, October 24, 2005 7:58 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705

There is ALWAYS a transaction whenever any kind of data modification occurs. Each DML statement starts an implicit transaction. You can choose to enlist one or more DML statements in an explicit transaction (using BEGIN TRAN / ROLLBACK / COMMIT), but there is no way to turn off the implicit transaction. That's one of the many features in SQL Server that helps to ensure the ACID properties.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #231615
Posted Wednesday, October 26, 2005 3:30 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, March 23, 2011 8:43 AM
Points: 145, Visits: 10

That's only true if you run in Implicit Transaction Mode (SET IMPLICIT_TRANSACTIONS ON).

Otherwise you have to execute a BEGIN TRAN to start a transaction.




Post #232323
Posted Wednesday, October 26, 2005 8:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, September 25, 2014 9:01 PM
Points: 1,113, Visits: 705

No, that's incorrect. Implicit transactions create an implicit multistatement transaction. But single statements still have their own implicit transactions. Take this batch, for instance:

INSERT Tbl VALUES ('w', 'x')
INSERT Tbl VALUES ('y', 'z')

Each of these inserts will start and end its own transaction (implicit -- because you are not explicitly starting a tran). EVERY DATA MODIFICATION USES A TRANSACTION. You cannot turn that off.

If you want to enlist both of these inserts in a single transaction (so that, e.g., if one fails the other can be rolled back with it), you have two choices:

BEGIN TRAN
-- do work
(COMMIT or ROLLBACK)

or:

SET IMPLICIT_TRANSACTIONS ON
-- do work
(COMMIT or ROLLBACK)


The difference between these is that if you use IMPLICIT_TRANSACTIONS, another multistatement tran starts automatically as soon as any data modification occurs. With BEGIN TRAN, you're controlling it, and you can let statements run using their own, atomic transactions.

Understanding how transactions work is extremely important when working with any DBMS. I highly recommend picking up Kalen Delaney's book, _Inside SQL Server 2000_, if any of this is new to you.


--
Adam Machanic
SQL Server MVP
SQLblog.com: THE SQL Server Blog Spot on the Web
Post #232467
« Prev Topic | Next Topic »

Add to briefcase ««123»»

Permissions Expand / Collapse