rollback - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

rollback

Author

Topic

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-17 : 00:27:29

The code inserts into table TABLES. TABLES has a primary key.
If the insert statement has no error it should insert 'ROWS INSERTED' to table CONTROL and update table DATETRACKING else insert 'ROWS NOT INSERTED' to table CONTROL and do not update table DATETRACKING.
1- I will like to improve my code if it is possible.
2- Can I use a rollback?

DECLARE @ROWCOUNT INT
DECLARE @ERROR INT
DECLARE @LASTDT DATETIME

SET @LASTDT =GETDATE()
BEGIN TRAN
insert tables
select 4

SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR
IF (@ERROR <> 0)
BEGIN
INSERT CONTROL
SELECT 'ROWS NOT INSERTED', @ROWCOUNT
END
ELSE
BEGIN
INSERT CONTROL
SELECT 'ROWS INSERTED', @ROWCOUNT
END
COMMIT TRAN

IF (@ROWCOUNT > 0)
BEGIN
UPDATE DATETRACKING
SET LASTDATEDL = GETDATE()
END

http://www.sqlserverstudy.com

Kristen
Test

22859 Posts

Posted - 2007-10-17 : 00:42:10

Why don't you put the COMMIT in the "Rows inserted" (ELSE) block, and the ROLLBACK in the "Rows NOT Inserted" block?

Do you intend your DATETRACKING update to happen whether there was error, or not?

Kristen

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-23 : 11:09:56

I intend to have DATETRACKING to happen when there is no error. Thx.

http://www.sqlserverstudy.com

Kristen
Test

22859 Posts

Posted - 2007-10-23 : 12:16:19

Needs to be in the "Rows inserted" part of the logic then, I reckon

funketekun
Constraint Violating Yak Guru

491 Posts

Posted - 2007-10-23 : 15:49:25

Like this? I think there is something wrong.

/*

DROP TABLE TABLES
CREATE TABLE TABLES (TABLESID INT PRIMARY KEY)
CREATE TABLE CONTROL (DESCS VARCHAR(50), COUNTROWS INT)
CREATE TABLE DATETRACKING (LASTDATEDL DATETIME)

SELECT * FROM TABLES
SELECT * FROM CONTROL
SELECT * FROM DATETRACKING

TRUNCATE TABLE TABLES
TRUNCATE TABLE CONTROL
TRUNCATE TABLE DATETRACKING
INSERT DATETRACKING
SELECT '2005-01-01'

*/

DECLARE @ROWCOUNT INT
DECLARE @ERROR INT
DECLARE @LASTDT DATETIME

SET @LASTDT = GETDATE()

BEGIN TRAN

INSERT TABLES
SELECT 1 UNION ALL
SELECT 4 UNION ALL
SELECT 56

SELECT @ROWCOUNT = @@ROWCOUNT, @ERROR = @@ERROR

IF (@ERROR <> 0)
BEGIN
ROLLBACK TRAN
END
ELSE
BEGIN
INSERT CONTROL
SELECT 'ROWS INSERTED', @ROWCOUNT

UPDATE DATETRACKING
SET LASTDATEDL = GETDATE()
COMMIT TRAN

END

http://www.sqlserverstudy.com

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources