alter proc us
@type varchar(10),
@mon money
as
begin
begin transaction
declare @id varchar(30)
select @id=cardid from cardinfo
insert into transinfo values(getdate(),@id,@type,@mon,'wu'))
commit transaction
print '123'
end
goalter trigger trans
on transinfo
for insert
as
begin
declare @type varchar(10),@cardid varchar(20),@mon money
select @type=transtype,@cardid=cardID,@mon=transMoney from inserted
if(@type='1')
begin
update cardinfo set balance=balance-5 where cardID=@cardid
update cardinfo set balance=balance-@mon where cardID=@cardid
end
else
update cardinfo set balance=balance+@mon where cardID=@cardid
end declare @id varchar(30)
select @id=cardid from cardinfo
insert into transinfo values(getdate(),@id,'1',20,'wu')
print '123'
exec us '1',20--就这里不明白了,在存储过程中插入,触发器运行,但是触发器中的update会违反check约束,结果触发器在
update cardinfo set balance=balance-@mon where cardID=@cardid这句违反之后就终止了,后面的不再执行,存储过程中的事务也被回滚,为什么啊?
但是如果是在存储过程中违反了check约束就不会回滚而且其他语句照常执行,可以commit或者rollback。现在我得判断触发器执行成功没有,遇到冲突就断了,怎么来判断?@@error就不提了,更别说在触发器中commit或者rollback了触发器check存储过程
@type varchar(10),
@mon money
as
begin
begin transaction
declare @id varchar(30)
select @id=cardid from cardinfo
insert into transinfo values(getdate(),@id,@type,@mon,'wu'))
commit transaction
print '123'
end
goalter trigger trans
on transinfo
for insert
as
begin
declare @type varchar(10),@cardid varchar(20),@mon money
select @type=transtype,@cardid=cardID,@mon=transMoney from inserted
if(@type='1')
begin
update cardinfo set balance=balance-5 where cardID=@cardid
update cardinfo set balance=balance-@mon where cardID=@cardid
end
else
update cardinfo set balance=balance+@mon where cardID=@cardid
end declare @id varchar(30)
select @id=cardid from cardinfo
insert into transinfo values(getdate(),@id,'1',20,'wu')
print '123'
exec us '1',20--就这里不明白了,在存储过程中插入,触发器运行,但是触发器中的update会违反check约束,结果触发器在
update cardinfo set balance=balance-@mon where cardID=@cardid这句违反之后就终止了,后面的不再执行,存储过程中的事务也被回滚,为什么啊?
但是如果是在存储过程中违反了check约束就不会回滚而且其他语句照常执行,可以commit或者rollback。现在我得判断触发器执行成功没有,遇到冲突就断了,怎么来判断?@@error就不提了,更别说在触发器中commit或者rollback了触发器check存储过程
but how to decide the trigger is fails?