Solved

Cancelling stored procedures through a trigger

Posted on 1998-12-11
8
381 Views
Last Modified: 2008-03-17
Hi,

I've got a problem with triggers. I would like to cancel a insert/update/delete in a trigger if some conditions are met. That isn't a problem, but according to the helpfiles a ROLLBACK TRANSACTION in the trigger should also cancel the calling stored procedure/batch. But when I'm trying this, the statements after the EXEC also gets executed.

I don't know if this is the same problem, but the @@ERROR variable doesn't have the error # from a RAISERROR in the trigger and the IDENTITY colum gets incremented although I cancel the insert/update with ROLLBACK TRANSACTION.

Example:
---------------------------------------------------------
create table test (id int identity,x int,constraint pkcl primary key clustered(id))
go

create trigger trig_test
on test
for insert,update,delete
as
  rollback tran
  raiserror('error',16,-1)
go

create procedure sp @id int output,@x int
as
  insert test values(@x)
  select @id=@@identity  <--- This shouldn't be executed?
  select @id,@@error     <--- Error # is 0 should be 50000?
go

declare @id int
exec sp @id output,10
select @id,@@error     <--- Error # is 0, should be 50000?
exec sp @id output,20
select @id,@@error     <--- @id (@@IDENTITY) is 2, why?
go
---------------------------------------------------------

Thanks,
pivar
0
Comment
Question by:pivar
8 Comments
 
LVL 7

Expert Comment

by:spiridonov
Comment Utility
When you rollbakc transactio identity value is not rolled back. This is documented 'feature'.
0
 
LVL 3

Expert Comment

by:hakyemez
Comment Utility
remove rollback tran in trigger only raiserror and return

create procedure sp @id int output,@x int
as
begin tran
  select @id=@@identity
  insert test values(@x)
  if @@error<>0
  begin
    rollback tran
    select @id,@@error
    return
  end
commit tran
go

0
 
LVL 22

Author Comment

by:pivar
Comment Utility
Hakyemez,

That's a possible solution, but I don't want to solve it like that. I've got several stored procedures and I don't wan't to copy this code to every sp, not unless it's necessary. I've read in the MSSQL help that it should work with ROLLBACK TRANSACTION in the trigger and for me this would be the best solution. Also, I've got a problem with the @@error-variable. It's always 0 at the position you propose, although I've raised an error! This was part of the question, if an error is raised the @@error-variable isn't 0 in the trigger, but in the sp it's 0.

/pivar
0
 
LVL 2

Expert Comment

by:tschill120198
Comment Utility
When I run the code you posted the rollback *does* cancel the calling sproc...  the output is:
    Msg 50000, Level 16, State 1
    error

Also, @@Error returns is 0 within the sproc because it is the value of the "select @id=@@identity" statement, not the raiserror in the trigger.  Replace the code in your sproc with these four lines to get the right @Error value:
  declare @Identity int, @error int
  insert test values(@x)
  select @Identity = @@identity, @error = @@error
  select @Identity as 'Identity', @error as 'Error'

What version of SQL are you running?

0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 22

Author Comment

by:pivar
Comment Utility
tschill,

Yes, you're right about the @@error, I was a bit ignorant there.
It's good to here the cancel works for you, I was afraid it was a typo error in the help...

I'm running MS SQL 6.5 with SP 4. Is it possible to see your config?

Thanks,
pivar
0
 
LVL 2

Accepted Solution

by:
tschill120198 earned 200 total points
Comment Utility
I'm running 6.50.409 (think that is SP4)...

Here's sp_configure, it's not a production box, just my dev machine.

name                                minimum     maximum     config_value run_value  
----------------------------------- ----------- ----------- ------------ -----------
affinity mask                       0           2147483647  0            0          
allow updates                       0           1           0            0          
backup buffer size                  1           32          1            1          
backup threads                      0           32          5            5          
cursor threshold                    -1          2147483647  -1           -1          
database size                       2           10000       2            2          
default language                    0           9999        0            0          
default sortorder id                0           255         61           61          
fill factor                         0           100         0            0          
free buffers                        20          524288      409          409        
hash buckets                        4999        265003      7993         7993        
language in cache                   3           100         3            3          
LE threshold maximum                2           500000      200          200        
LE threshold minimum                2           500000      20           20          
LE threshold percent                1           100         0            0          
locks                               5000        2147483647  5000         5000        
LogLRU buffers                      0           2147483647  0            0          
logwrite sleep (ms)                 -1          500         0            0          
max async IO                        1           1024        8            8          
max lazywrite IO                    1           1024        8            8          
max text repl size                  0           2147483647  65536        65536      
max worker threads                  10          1024        255          255        
media retention                     0           365         0            0          
memory                              2800        1048576     8192         8192        
nested triggers                     0           1           1            1          
network packet size                 512         32767       4096         4096        
open databases                      5           32767       20           20          
open objects                        100         2147483647  500          500        
priority boost                      0           1           0            0          
procedure cache                     1           99          30           30          
Protection cache size               1           8192        15           15          
RA cache hit limit                  1           255         4            4          
RA cache miss limit                 1           255         3            3          
RA delay                            0           500         15           15          
RA pre-fetches                      1           1000        3            3          
RA slots per thread                 1           255         5            5          
RA worker threads                   0           255         3            3          
recovery flags                      0           1           0            0          
recovery interval                   1           32767       5            5          
remote access                       0           1           1            1          
remote conn timeout                 -1          32767       10           10          
remote login timeout                0           2147483647  5            5          
remote proc trans                   0           1           0            0          
remote query timeout                0           2147483647  0            0          
remote sites                        0           256         10           10          
resource timeout                    5           2147483647  10           10          
set working set size                0           1           0            0          
show advanced options               0           1           1            1          
SMP concurrency                     -1          64          0            1          
sort pages                          64          511         64           64          
spin counter                        1           2147483647  10000        0          
tempdb in ram (MB)                  0           2044        0            0          
time slice                          50          1000        100          100        
user connections                    5           32767       15           15          
user options                        0           4095        0            0          

0
 
LVL 22

Author Comment

by:pivar
Comment Utility
Hi,

I really checked the versionnumber and of course I had just upgraded in my sleep 8-). I had sp3, and with sp4 it works as it should.

But I'm curious, do you really have version 6.50.409? According to sp4 (the one I have anyway), it should be version 6.50.281.

Thanks,
pivar
0
 
LVL 2

Expert Comment

by:tschill120198
Comment Utility
Had to do some asking around on this... we're using a beta-SP5, not publicly released.
0

Featured Post

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

Performance is the key factor for any successful data integration project, knowing the type of transformation that you’re using is the first step on optimizing the SSIS flow performance, by utilizing the correct transformation or the design alternat…
Introduction In my previous article (http://www.experts-exchange.com/Microsoft/Development/MS-SQL-Server/SSIS/A_9150-Loading-XML-Using-SSIS.html) I showed you how the XML Source component can be used to load XML files into a SQL Server database, us…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now