我在msss的帮助中找了好长时间,发现根本就没有这两个概念。
所谓语句级触发器也就是说一个触发器在被一条语句点火的情况下,只触发一次,不管这条语句相关的数据有几行。
例如:
有表testa {a,b} testb{a,b}
触发器
CREATE trigger [tri_testa] on testa
FOR UPDATE
as
declare
@a int,
@b int
if update(b)
begin
select @a = a, @b = b from inserted
begin TRANSACTION
insert into testb values (@a, @b) IF (@@ERROR <> 0) ROLLBACK TRANSACTION
else COMMIT TRANSACTION
end
我用一条语句改写表testa中的10条纪录,而testb中插入的只有1条纪录。
改写为:
insert into testb select a, b from inserted
就可以改变以上情况,但是实际中我必须要把每条纪录分开,根据另外一个表中具体情况作处理。我估计药用显示游标,但是不会用,也不知道在msss中是不是这样叫。请帮忙,谢谢!
所谓语句级触发器也就是说一个触发器在被一条语句点火的情况下,只触发一次,不管这条语句相关的数据有几行。
例如:
有表testa {a,b} testb{a,b}
触发器
CREATE trigger [tri_testa] on testa
FOR UPDATE
as
declare
@a int,
@b int
if update(b)
begin
select @a = a, @b = b from inserted
begin TRANSACTION
insert into testb values (@a, @b) IF (@@ERROR <> 0) ROLLBACK TRANSACTION
else COMMIT TRANSACTION
end
我用一条语句改写表testa中的10条纪录,而testb中插入的只有1条纪录。
改写为:
insert into testb select a, b from inserted
就可以改变以上情况,但是实际中我必须要把每条纪录分开,根据另外一个表中具体情况作处理。我估计药用显示游标,但是不会用,也不知道在msss中是不是这样叫。请帮忙,谢谢!
CREATE trigger [tri_testa] on testa
FOR UPDATE
as
declare
@a int,
@b int
if update(b)
begin
begin TRANSACTION
insert into testb select a,b from inserted
IF (@@ERROR <> 0) ROLLBACK TRANSACTION
else COMMIT TRANSACTION
end
FOR UPDATE
as
declare
@a int,
@b int
if update(b)
begin
-- begin TRANSACTION --这一句不能要
insert into testb select a,b from inserted
IF (@@ERROR <> 0) ROLLBACK TRANSACTION
-- else COMMIT TRANSACTION --这一句也不必
end
FOR UPDATE
as
insert into testb select a,b from inserted where not exists (
select * from testb where a=i.a and b=i.b) IF (@@ERROR <> 0) ROLLBACK TRANSACTION
go
FOR UPDATE
as
declare
@a int,
@b int
if update(b)
begin
DECLARE cur CURSOR
FOR select a, b from inserted
FOR READ ONLY
open cur
fetch NEXT FROM CUR INTO @A,@B
WHILE @@FETCH_STATUS = 0
BEGIN
IF @A<>1 AND @A*@B=10 ---可能使更多的条件
BEGIN
insert into testb values (@a, @b)
IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
END
fetch NEXT FROM CUR INTO @A,@B
END
CLOSE cur
DEALLOCATE curend
FOR UPDATE
as
insert into testb select a,b from inserted
where A>0 AND not exists (
select * from testb where a=i.a and b=i.b) IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
insert into testb select a=1,b from inserted
where A=0 AND not exists (
select * from testb where a=i.a and b=i.b) IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
insert into testb select -a,b from inserted
where A<0 AND not exists (
select * from testb where a=i.a and b=i.b) IF (@@ERROR <> 0)
BEGIN
ROLLBACK TRANSACTION
RETURN
END
go