Carlo Romagnano (12/20/2010)
ian.grace (12/20/2010)
I don't understand how the first transaction could succeed given the attempt to insert the null value into the not-null field "a". Also, why is the last insert of the second transaction, VALUES(2 ,'one'), invalid? Help, I'm confused.As posted above by vk-kirov and others, the explanation is terrible with a lot of mistakes.
Ian, I know you've received an explanation already, but I wanted to add to that. You can control the behavior on error as follows in the comments below.
BEGIN TRAN
INSERT INTO [Table1]( a,b)
VALUES(1 ,'one')
INSERT INTO [Table1]( a,b)-- Insert fails because the NOT NULL constraint on column [a]
VALUES(NULL ,'five')
-- Even though the above insert STATEMENT fails, execution of the BATCH continues.
-- To halt execution after error use SET XACT_ABORT = ON (stops the BATCH from executing)
-- To reroute execution after error use TRY CATCH (recommended) or check @@Error and use GOTO
INSERT INTO [Table1]( a,b)
VALUES(4 ,'join1')
INSERT INTO [Table1]( a,b])
VALUES(2 ,'join3')
COMMIT TRAN
GO