Solved

Cancelling stored procedures through a trigger

Posted on 1998-12-11
8
387 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
8 Comments
 
LVL 7

Expert Comment

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

Expert Comment

by:hakyemez
ID: 1092218
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
ID: 1092219
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
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 2

Expert Comment

by:tschill120198
ID: 1092220
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
 
LVL 22

Author Comment

by:pivar
ID: 1092221
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
ID: 1092222
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
ID: 1092223
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
ID: 1092224
Had to do some asking around on this... we're using a beta-SP5, not publicly released.
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 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