|
|
|
|
|
In ASE, if an error with a severity larger than 10 is encountered and you are inside a trigger, or inside a stored procedure that is directly or indirectly fired from the trigger, control is handed back to the caller, all the way up in the stack until the trigger is reached. If a rollback was performed, the flow of control ends in the trigger. If no rollback was performed, the flow of control even returns to the uppermost calling stored procedure.
In MS SQL Server, execution ends with an implicit rollback at the moment that the error occurs.
In both systems, execution ends immediately when the severity of the error is 10 or lower.
Examples
Run the example below on both systems.
The ‘after print’ statements are not fired on MS SQL Server.
Remove the two marked rollback statements and run the example again.
Now ASE even returns to P1.
Remove the delete statement marked with ‘example 1’.
Activate the update statement marked with ‘example 2’.
Run the example again
Now both systems behave alike. The ‘after print’ statements are not fired.
create table T1 (i int)
alter table T1 add constraint PK_T1 primary key (i)
go
create table T2 (i int, j int)
alter table T2 add constraint FK_T2_T1 foreign key (i) references T1 (i)
go
insert into T1 values (1)
insert into T2 values (1, 0)
go
create proc P1 as
declare
@Err int
begin
print 'P1 before'
begin tran
update T2 set j = i
select @Err = @@Error
if @Err != 0
begin
if @@TranCount > 0 rollback tran
print 'P1 after - error'
return @Err
end
if @@TranCount > 0 commit tran
print 'P1 after'
return 0
end
go
create proc P2 as
declare
@Err int
begin
print 'P2 before'
delete from T1 where i = 1 -- example 1, severity > 10
-- update T2 set j = j / 0 -- example 3, severity <= 10
select @Err = @@Error
if @Err != 0
begin
if @@TranCount > 0 rollback tran -- example 2, remove this line
print 'P2 after - error'
return @Err
end
print 'P2 after'
return 0
end
go
create trigger G_T2_U on T2 for update as
declare
@Ret int
begin
print 'G_T2_U before'
exec @Ret = P2
if @Ret != 0
begin
if @@TranCount > 0 rollback tran -- example 2, remove this line
print 'G_T2_U after - error'
return
end
print 'G_T2_U after'
return
end
go
exec P1
go
drop trigger G_T2_U
drop proc P1
drop proc P2
drop table T2
drop table T1
go
Remark
A ‘try – catch’ construction can be used in MS SQL Server to make it behave like ASE.
create proc P2 as
declare
@Err int
begin
print 'P2 before'
begin try
delete from T1 where i = 1
end try
begin catch
set @Err = ERROR_NUMBER()
if @@TranCount > 0 rollback tran
print 'P2 after - error'
return @Err
end catch
print 'P2 after'
return 0
end
go