Problem With Rollback Trigger

Jul 20, 2005

Hi, Everybody,

I'm a Hungarian SQL user and I need a little help for SQL Server 7 !

I protect my table against bad data with a trigger. I use ROLLBACK and
RAISERROR statement in this trigger. Users can get my error message
after manual input, but the stored procedure always cancel because of
ROLLBACK. So the input program dont't have chance to analyze the error
message. I could work without ROLLBACK, but it wouldn't be the best
solution.

What's the correct solution with ROLLBACK statement?

View 3 Replies


ADVERTISEMENT

Trigger Which Make Rollback...

Jan 15, 2007

Hi,

I need help for this:
I need to make trigger which makes rollback
if INSERT- or UPDATE-statement try to make
dublicate row (col1+col2)
I try this, but it don't work:


CREATE TRIGGER trg_col1_col2_check
ON TABLE1
AFTER INSERT,UPDATE
AS
BEGIN
SET NOCOUNT ON
IF (SELECT DISTINCT 1
FROM TABLE1
GROUP BY col1, col2
HAVING count(*) > 1) = 1
BEGIN
IF (SELECT
case
when (i.col1 = ''
AND i.col2 = '') then 0
else 1
end
FROM INSERTED i) = 1
BEGIN
Print 'Col1 and col2 Already exist'
ROLLBACK
END
END
END

View 8 Replies View Related

Rollback In A Trigger??? (explicit/autocommit Transactions)

Jun 21, 2006

I€™m using triggers for some more advanced integrity check. The problems is that the same trigger can be run from explicit transaction (this is when I start transaction from .NET) and as autocommit transaction ( very rare, only when we do some maintenance directly with SQL statements).

Currently if I want to rollback transaction from trigger I only issue RAISERROR statements, then .NET application catches this error and generates rollback. But the problem is if trigger is raised from some SQL statements outside .NET application (normally some maintenance work direct from SQL manager ) in that case error is generated but there is no rollback.

Is there any way to distinguish if transaction in trigger is explicit or autocommited, because for autocommited transaction I also need use ROLLBACK TRANSACTION?

I€™m using SQL 2005!

Best regards
edvin

View 6 Replies View Related

Can't Rollback Alter Database Statement DDL Trigger

Feb 26, 2008

Recently I created a DDL Server-scope trigger using the following:

create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go

Then I ran the following script:

alter database [test] modify file (name=test', maxsize=2028mb);


The result was:


Msg 3609, Level 16, State 2, Line 1

The transaction ended in the trigger. The batch has been aborted.

database change stopped by stop_alter_database

The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?

View 2 Replies View Related

SQL Server 2014 :: Transaction Rollback When Multiple Threads Are Inserting Records Into Table Because Of Trigger

Jun 18, 2014

I have two tables called ECASE and PROJECT

In the ECASE table there is trigger to get the max value of case_id column in ecase based on project and increment one to that case_id value and insert into ecase table .

When we insert a new record to the ECASE table this trigger calls and insert the case_id column value.

When i run with multiple threads , the transaction is rolled back because of trigger . The reason is , on the project table the lock is happening while getting the max value of case_id column based on project.

I need to prevent the deadlock .

View 3 Replies View Related

SQL Server Admin 2014 :: Server Level Trigger To Log Activity And Rollback After Logging Information

Sep 8, 2015

I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:

create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end

I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.

View 8 Replies View Related

How To Rollback SQL

Aug 12, 2007

Hello experts, I made a huge mistake on a production server, ran an update without the WHERE clause. UPDATE myTableSET Field1 = 'SOmetext',Field2 = 'sometext2'Is there a way to rollback? I read something a while ago about transaction logs.SOS!Thanks 

View 4 Replies View Related

Rollback

Sep 27, 2007

HI,
 Suppose I execute any transaction say inserted some rows.
I want  to delete my previous transaction using rollback.
What is the proper syntax for that.
In Query Window:
Insert ---------- executed
rollback.
it was asking for name of the transaction. How to give that.
Help me out
Thanks
Bye
 
 
 

View 1 Replies View Related

Why Not Rollback?

Nov 12, 2003

This is my stored procedure. Problem is that a rollback does not happen when there is an error ? How can this be, am starting and ending the transaction?

CREATE Procedure opretKunde
(
@cvr int,
@url varchar(200),
@firmaNavn varchar(50),
@adresse varchar(150),
@postnr int,
@fax int,
@tlf int,
@email varchar(200),
@bruger varchar(20),
@kode varchar(32)
)
AS

BEGIN TRANSACTION

INSERT INTO login
(
brugernavn,
kodeord,
type
)
VALUES
(
@bruger,
@kode,
'k'
)

INSERT INTO kunde
(
cvr,
url,
navn,
fax,
tlf,
email,
brugernavn
)

VALUES
(
@cvr,
@url,
@firmaNavn,
@fax,
@tlf,
@email,
@bruger
)

INSERT INTO adresse
(

type,
cvr,
adresse,
postnr
)
VALUES
(

'b',
@cvr,
@adresse,
@postnr
)

COMMIT

GO

View 1 Replies View Related

Rollback

Aug 10, 2000

Hi,

Can I rollback a database if I have transaction logs?

For example, I want to rollback the database without all the transactions that occurs within the last 10 days.

Thank you.

View 3 Replies View Related

Rollback

May 18, 2000

I tried killing a rollback several times, but it would not disapear.
I came back the next day, it is still there.

Anyone knows how to get rid of a rollback?

Thanks in advance.

View 2 Replies View Related

Rollback

May 5, 2000

Hi:

I have rollback on one of the users. I tried killing
her process through Enterprise Manager and Query Analyzer,
but I still can see her process on the screen. However, her
login is grayed out after I execute the kill query.

I have encountered this problem several times, and the only way
I can get clear the rollback is by rebooting the server.

Does anyone have a sugeestion on how to clear the rollback
without rebooting.

Please advise if you have any suggestion.

Thanks in advance.

View 2 Replies View Related

DTS ROLLBACK

Apr 5, 2000

Hi All
A question on DTS ROLLBACK CAPABILITY.
Is it possible to setup a DTS pkg to roll back the loaded files if a step fails?
If a step fail records inserted by Data Pump to a table should be removed.
Is this possible? If so how can we achieve this?

Any advice is appreciated.

Thanks

View 5 Replies View Related

Rollback

Dec 10, 2003

when i give rollback in my QA it gives an error saying (something like this)
- no begin transaction specified

In oracle it works fine why?

View 6 Replies View Related

Rollback

Jan 15, 2004

how to rollback(undo) record was i deleted???

pls tell me...

View 1 Replies View Related

Rollback

May 22, 2004

Hello every body,
I would like to know the procedure, that how to rollback my transaction in sql server 2000

View 5 Replies View Related

ROLLBACK

Jun 29, 2006

Hi,

i wanna know something, i inserted some rows of data to a table, but i wanna undo those insertion. How can i do it?

thanks

View 9 Replies View Related

Is Rollback Possible?

Aug 29, 2006

hi friends,

is it possible to roll back a transaction here? i heard that it will be possible only if we wrote the code only in certain block.please tell me the block.


vinod

View 5 Replies View Related

Rollback

Oct 17, 2007

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

View 4 Replies View Related

Rollback

Jan 28, 2008

Hi All,

Can anyone please tell how to rollback a transaction or a command.

TIA

RKNAIR

View 1 Replies View Related

Rollback SP4

May 12, 2008



Hi all

I have SQL server 2000 with SP4,
1.Some of the reason,I want roll back SP4 and I want replace SP3.
2.Is it true SP4 slow down the Quey performance compare with SP3.

any suggestion appreciated.

View 1 Replies View Related

My Rollback Does Not Work

May 30, 2007

My rollback does not work
In my SP I want any of cmdS,cmdS2,cmdS3,cmdS4 produces error Rollback must be executed for all of cmdS,cmdS2,cmdS3,cmdS4 . I tested for error producing situation but no rollbak occured.
How can I solve this problem. Thanks.
Below is my SP .
..........
..........
.......... 
begin transaction
..........
..........If Len(ltrim(rtrim(@cmdS)))>0
execute(@cmdS)
 If Len(ltrim(rtrim(@cmdS2)))>0
execute(@cmdS2) If Len(ltrim(rtrim(@cmdS3)))>0
execute(@cmdS3)
 If Len(ltrim(rtrim(@cmdS4)))>0
execute(@cmdS4)
If Len(ltrim(rtrim(@cmdS)))>0 or Len(ltrim(rtrim(@cmdS2)))>0 or Len(ltrim(rtrim(@cmdS3)))>0 or Len(ltrim(rtrim(@cmdS4)))>0
Beginif @@ERROR <>0
begin
rollback transaction
set @Hata = 'Error !'
end
Else
Beginset @Hata = 'Sucessfully executed :)' End
End
commit transaction
RETURN
 
 

View 1 Replies View Related

What To Do After A Transaction Rollback?

Dec 9, 2003

Hi!

I just made a transaction which writes and reads some data from a database as one unit. Now, I would like to know, what happens, if this transaction does not commit, but instead performs a rollback? Should I use a boolean loop which keeps on trying the transaction until it commits or is there an alternative? I would appreciate any suggestions or help!

Thanks!
Timothy

View 1 Replies View Related

Timeout On Rollback

May 17, 2004

I keep getting timeout expired when trying to rollback a transaction (have not tried to commit yet). The script hangs for around 30 secs, then fails.

Now, i've seen there's a command timeout for sqlcommands which defaults to 30 secs, but cannot find anything similar for sqltransaction...

Anyone can help?

Thanks. -julio

View 3 Replies View Related

SQL Transactions RollBack...

Dec 17, 2004

Hello:

I am just starting to falmiliarize myself with SQL transactions... I just created an SQL transaction... The first statement gets a value, if the value equals "" then the second statement executes... So if the value <> "" Then the second statement wont execute... What would happen in this scenario if the .Rollback is triggered? Heres my code:

Try
conSqlConnect.Open()
objTransaction = conSqlConnect.BeginTransaction
cmdSelect.Transaction = objTransaction
cmdInsert.Transaction = objTransaction
dtrdatareader = cmdSelect.ExecuteReader()
While dtrdatareader.Read()
varCheckNumber1 = dtrdatareader("Status")
End While
dtrdatareader.Close()
If varCheckNumber1 = "" Then
cmdInsert.ExecuteNonQuery()
End If
objTransaction.Commit
Catch
objTransaction.RollBack
Return "00"
Finally
If conSQLConnect.State = ConnectionState.Open Then
conSqlConnect.Close()
End If
End Try


Any Ideas?

View 5 Replies View Related

Rollback Issue

Mar 30, 2001

Hello list,

I've some problem in SQL Server 7.0 and hope some one out there will help me with it. The problem is as follows:

When I view a proces in the process info screen, I see one process says "Rollback" in the status window and the spid is greyed out like a system process. In the command window it says, Select and wait type is "Exchange". This is taking too much cpu cycles and causing much of the server to perform very slow.

I tried to kill this session from the ISQL and Enterprise manager, But to still see this process running on with out getting killed. Also this process keeps coming.

Please advice me how to get rid of this and why is it ocurring again and again.

Thanks in advance.

Khan

View 2 Replies View Related

Automatic Rollback

May 1, 2001

I am getting an automatic rollback in a transaction that fails to insert a row, via a trigger, due to a duplicate key. The transaction is automatically rolled back and unless the error correctly handled a 'partial update' occurs. Why in the example below is the transaction automatically rolled back (statements 1 to 3) and is there any way of knowing what errors generate an auto rollback. For example if the duplicate key error occurs on the insert into table B below, and not the triggered update, then no auto rollback occurs.

Begin Tran -- 1
insert into A values ('A1') -- 2 updates OK
insert into B values ('B1') -- 3 updates OK
-- triggers insert into B_Hist -- 4 Fails with Duplicate Key error
insert into C values ('C1') -- 5 updates OK
Commit Tran -- 6 3902 error No corresponding Begin

Thanks.

View 3 Replies View Related

Rollback Process--please Help

Aug 16, 2000

Hi,

I have this weird situation on one of the production database.
Somebody ran a sql script couple days ago and must have hit cancelled. Somehow this script is still rolling back. The script is actually very simple statement..should not take more than couple miliseconds to run. Is there any way to see whether rollback is really in process or this process has simply hung? is there any other way to get rid of this rollback without stopping and restarting sql server services? Can it cause problems in getting sql server up again if I restart sql server services?

View 7 Replies View Related

Rollback Optimization

Apr 28, 2000

This may sound a little silly, but does anyone have any words of wisdom on how to optimize a server/database for minimim rollback? We have some multimillion row tables we were trying to do updates against, and after several days they increased the size of the transaction log to the point they filled up the drive the database files/logs were on. We've now been running a rollback for about five days. I'd like to make sure this doesn't happen again.

View 1 Replies View Related

What Will Happen -- ROLLBACK Or NONE?

Jul 14, 1999

Hi,

what will happen in the following occasion?

checkpoint --> begin tran --> system failure

In MOC (SQL 7.0 Implemetation), it says there will be rollback, but in my humbel opinion, there should be nothing happened.

I think there was no dirty writing in this case. No data pages written, no log
pages written to disk. Am I right? Then, there might be nothing happened
instead of ROLLBACK.

If I am wrong, would you please let me know what is wrong in my thougt?

Thanks in advance,

View 2 Replies View Related

Rollback Of Sp Calling Another Sp

Mar 10, 2006

If you have a begin tran in a sp that calls another sp and that called sp fails. can you execute a roll back from the called sp, or should it return and error which will roll back the begin tran in the original sp

View 1 Replies View Related

SQL Rollback Issues

Nov 20, 2004

Hello all,

I have been informed that most (if not some) RDBMS are faced with problems with the SQL ROLLBACK (also known as undo) command. I have been searching online, but have not found any useful relevant sources on what this issues are.

I am wondering if their are any experienced MS SQL Server (or any other RDBMS) users who might be able to provide feedback on this matter.

Krz

View 1 Replies View Related

Despirately Need Help! Must Rollback But Don't Know How!!!

Oct 6, 2005

Don't ask, but I iadvertantly managed to delete 57,000+ records in the EM console and I need to roll back the database a few minutes to recover said information. I do not care about transactions that have occured since due to shutting the system down immediately upon realizing what I did. I have however never had the need to roll back the database before, so I have experience doing so. Any help is desperately needed and greatly appreciated!!!

View 14 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved