transactions & how to use COMMIT and ROLLBACK
jim cox
suppose that i have the following three (obviously contrived) tables :
create table languages
(
PRIMARY KEY(id_language) ,
id_language integer IDENTITY(1,1) NOT NULL ,
language varchar(50) NOT NULL
)
create table people
(
PRIMARY KEY(id_person) ,
id_person integer IDENTITY(1,1) NOT NULL ,
name varchar(50) NOT NULL
)
create table language_skills
(
FOREIGN KEY(fk_person) REFERENCES people(id_person),
FOREIGN KEY(fk_language) REFERENCE languages(id_language),
id_language_skill IDENTITY(1,1) NOT NULL ,
fk_person integer NOT NULL ,
fk_language integer NOT NULL
)
and that i'm trying to accept a "batch" update of a person's language skills
from an outside source that replaces all of the person's existing language
skills. my original thought was (assuming the person's id in this case is
99, and that we're adding french (id=1), english (id=2), and chinese (id=3)):
DELETE FROM language_skills WHERE fk_person=99
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 1)
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 2)
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 3)
that works fine, but i'd like to wrap it up into a TRANSACTION for integrity
reasons (?). my first attempt at it was:
BEGIN TRANSACTION
DELETE FROM language_skills WHERE fk_person=99
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 1)
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 2)
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 3)
COMMIT TRANSACTION
again, this works fine, but i was surprised to find that when i try the
following, the transaction is not rolled back, and the person ends up with two
language_skill rows (for french & chinese):
BEGIN TRANSACTION
DELETE FROM language_skills WHERE fk_person=99
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 1)
-- intentionally 'bad' fk_language of 123456 used on next line
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 123456)
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 3)
COMMIT TRANSACTION
i expected the 2nd INSERT's foreign key constraint violation to force the
TRANSACTION to ROLLBACK to the original state. i then settled on the following
code which works as i originally expected, but seems awkward:
DECLARE @nErrorTotal integer
SELECT @nErrorTotal = 0
BEGIN TRANSACTION
DELETE FROM language_skills WHERE fk_person=99
IF 0 != @@ERROR
SELECT @nErrorTotal=@nErrorTotal + 1
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 1)
IF 0 != @@ERROR
SELECT @nErrorTotal=@nErrorTotal + 1
-- intentionally 'bad' fk_language of 123456 used
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 123456)
IF 0 != @@ERROR
SELECT @nErrorTotal=@nErrorTotal + 1
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 3)
IF 0 != @@ERROR
SELECT @nErrorTotal=@nErrorTotal + 1
IF 0 = @nErrorTotal
COMMIT TRANSACTION
ELSE
ROLLBACK TRANSACTION
am i missing an easy solution here? or is this how TRANSACTIONS have to be
coded? any help or information would be greatly appreciated.
+--------------------------------------------+
+ jim cox
+
+ j...@mars.superlink.net
+
+--------------------------------------------+
sputman
In Article<EB40J...@nonexistent.com>, <j...@mars.superlink.net> writes:
> Newsgroups: comp.databases.ms-sqlserver,microsoft.public.sqlserver.programming
> Path: news1.stny.lrun.com!news-out.internetmci.com!pull-feed.internetmci.com!newsfeed.internetmci.com!newsfeed.internetmci.com!portal.gmu.edu!hearst.acc.Virginia.EDU!uunet!in1.uu.net!165.254.2.53!nonexistent.com!not-for-mail
> From: j...@mars.superlink.net (jim cox)
> Subject: transactions & how to use COMMIT and ROLLBACK
> X-Newsreader: News Xpress 2.01
> X-Complaints-To: Email ab...@news2.new-york.net if this posting is inappropriate
> Organization: nai
> Message-ID: <EB40J...@nonexistent.com>
> X-Trace: 865189549 19770 (None) [207.111.90.133]
> X-Nntp-Posting-Host: jim2.nardoni.com
> Date: Sun, 1 Jun 1997 18:25:48 GMT
> Lines: 102
> Xref: news1.stny.lrun.com comp.databases.ms-sqlserver:7638 microsoft.public.sqlserver.programming:4587
Jim,
That looks pretty much like I have done in the past. You must check
after each command for errors. Unless there is a need to know how
many errors there were, I will use a goto label ...
BEGIN TRANSACTION
DELETE FROM language_skills WHERE fk_person=99
IF 0 != @@ERROR
goto Error_Trap
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 1)
IF 0 != @@ERROR
goto Error_Trap
-- intentionally 'bad' fk_language of 123456 used
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 123456)
IF 0 != @@ERROR
goto Error_Trap
INSERT INTO language_skills(fk_person, fk_language) VALUES(99, 3)
IF 0 != @@ERROR
goto Error_Trap
COMMIT TRANSACTION
RETURN
Error_Trap:
ROLLBACK TRANSACTION
PRINT "some error message"
RETURN 99
Tibor Karaszi
Jim,
In 6.5, you have a new SET parameter:
SET XACT_ABORT ON (or OFF)
this will make SQL Server abort (rollback) your transaction whenever an
error is returned.
Search "What's new 6.5" in books online for more information.
Personally, I prefer "the old" style...
--
Tibor Karaszi, ti...@cornerstone.se
Instructor (MCT, MCSE, MCSD)
Cornerstone Sweden AB
jim cox <j...@mars.superlink.net> wrote in article
<EB40J...@nonexistent.com>...
Karl Nilsson
You said this is obviously contrived".
How about nested transactions?
Either:
1. the transaction is nested - check the nesting level.
2. IDENTITY is always an issue. check @@IDENTITY and see where that takes
you on BOOKS ONLINE and coding samples... I use IDENTITY extensively as
Primary Key - Foreign Key (PK-FK). Use MS SQL Server SP2 for SQL Server
6.5 on NT 4.0 at least. Do not trust IDENTITIES without that.
3. If you are not checking @@<global variable name> (ED: BOOKS ONLINE -
LOOK UP "VARIABLE" then go to GLOBAL VARIABLES.) .. , then you are not
doing good housekeeping in your "procedure".
Karl Nilsson
ka...@usinternet.com but please "reply to newsgroup"
hey, we're all just learning how this works...
hey you all: it takes a fair amount of effort ( say half- an -hour) to just
do this. thanks for tolerating my rudimentary efforts, just learning how,
..
// eof
// kn