触发器可以不用游标,直接updateCREATE trigger t_insert on pointper instead of insert as update pointper set pointId=cast((cast(pointId as int)+1) as varchar(10)) where cast(pointId as int)>=cast((select pointId from inserted) as int) insert into pointper select * from inserted
用intead of 的意思是屏蔽掉我在程序中的SQL语句,,但我要通过SQL语句的返回值来控制程序的 所以我在问题那说了不能用INTEAD OF
用for的话,这样可以吗?drop table pointpercreate table pointper(flowId varchar(10),pointId varchar(10),name varchar(10))insert into pointper (flowId ,pointId,name) values ('1','1','a1') insert into pointper (flowId ,pointId,name) values ('1','2','a2')create trigger t_insert on pointper for insert as delete from pointper where flowId=(select flowId from inserted) and pointId=(select pointId from inserted) and name=(select name from inserted) update pointper set pointId=cast((cast(pointId as int)+1) as varchar(10)) where cast(pointId as int)>=cast((select pointId from inserted) as int) insert into pointper select * from inserted 测试 ------------------ insert into pointper (flowId ,pointId,name) values ('1','2','a3')select * from pointper------------------------- 1 1 a1 1 3 a2 1 2 a3------------------------ 这样用for感觉怪怪的,如果表里有主键,只要delete from table where主键=(select 主键 from inserted) 这样就可以了-------------------- 用intead of 的意思是屏蔽掉我在程序中的SQL语句,,但我要通过SQL语句的返回值来控制程序的 所以我在问题那说了不能用INTEAD OF对这句话还不是很理解,anyway,你说不能用就不能用,只能这样处理了 不过游标真的可以不用
for和after没有区别完整的语句是for after 如果你只写for,那么after石默认的这个sql的手册上都写着那
那例如:我在程序里有DELETE FROM TB WHERE ID = 1 这样一条语句 我的触发器是删除后在触发用的是AFTER ,数据是删除了,但程序的返回值是0 我的触发器是:CREATE trigger t_delect on point after delete as declare @id int,@orderorder int,@name varchar(50),@purview varchar(100),@dabletime varchar(100),@funtion varchar(1000),@personname varchar(100) declare point1 cursor for select * from deleted order by id,orderorder
open point1 fetch next from point1 into @id ,@orderorder,@name,@purview,@dabletime,@funtion,@personname while @@fetch_status=0 begin update point set orderorder=cast(orderorder as int)-1 where id=@id and orderorder>@orderorder fetch next from point1 into @id,@orderorder,@name,@purview,@dabletime,@funtion,@personname endclose point1 deallocate point1
语句是DELETE FROM POINT WHERE ID = 1
我用删除的触发器t_delect后,在查询分析器里运行 select id,orderorder,name from point where id = 2 order by id,orderorder id orderorder name null null null insert into point (id,orderorder,name) values ('2','1','1') insert into point (id,orderorder,name) values ('2','2','2') insert into point (id,orderorder,name) values ('2','3','3') insert into point (id,orderorder,name) values ('2','4','4') insert into point (id,orderorder,name) values ('2','5','5') select id,orderorder,name from point where id = 2 order by id,orderorder id orderorder name 2 1 1 2 2 2 2 3 3 2 4 4 2 5 5 delete from point where id = 2 and orderorder = 5(所影响的行数为 0 行) (所影响的行数为 1 行) select id,orderorder,name from point where id = 2 order by id,orderorder id orderorder name 2 1 1 2 2 2 2 3 3 2 4 4 2 5 5 请问我从删除到显示(所影响的行数为 0 行)(所影响的行数为 1 行)的过程是怎样的??
修改一下触发器 create trigger t_delect on point for delete as if exists(select 1 from point where id=2 and orderorder=5) print '删除的记录还存在' else print '删除的记录不存在了'运行 delete from point where id = 2 and orderorder = 5结果 ---------------------- 删除的记录不存在了(所影响的行数为 1 行) -----------------------信息先是显示trigger中的操作再是显示delete/update/insert的操作影响的行数
instead of insert
as
update pointper set pointId=cast((cast(pointId as int)+1) as varchar(10))
where cast(pointId as int)>=cast((select pointId from inserted) as int)
insert into pointper select * from inserted
insert into pointper (flowId ,pointId,name) values ('1','2','a2')
测试
------------------
insert into pointper (flowId ,pointId,name) values ('1','2','a3')
-----------------
flowId pointId name
1 1 a1
1 3 a2
1 2 a3
----------------
所以我在问题那说了不能用INTEAD OF
insert into pointper (flowId ,pointId,name) values ('1','2','a2')create trigger t_insert on pointper
for insert
as
delete from pointper where flowId=(select flowId from inserted) and pointId=(select pointId from inserted) and name=(select name from inserted)
update pointper set pointId=cast((cast(pointId as int)+1) as varchar(10))
where cast(pointId as int)>=cast((select pointId from inserted) as int)
insert into pointper select * from inserted
测试
------------------
insert into pointper (flowId ,pointId,name) values ('1','2','a3')select * from pointper-------------------------
1 1 a1
1 3 a2
1 2 a3------------------------
这样用for感觉怪怪的,如果表里有主键,只要delete from table where主键=(select 主键 from inserted)
这样就可以了--------------------
用intead of 的意思是屏蔽掉我在程序中的SQL语句,,但我要通过SQL语句的返回值来控制程序的
所以我在问题那说了不能用INTEAD OF对这句话还不是很理解,anyway,你说不能用就不能用,只能这样处理了
不过游标真的可以不用
和
instead offor insert==after insert
如果你只写for,那么after石默认的这个sql的手册上都写着那
我的触发器是删除后在触发用的是AFTER ,数据是删除了,但程序的返回值是0
我的触发器是:CREATE trigger t_delect on point
after delete
as
declare @id int,@orderorder int,@name varchar(50),@purview varchar(100),@dabletime varchar(100),@funtion varchar(1000),@personname varchar(100)
declare point1 cursor for select * from deleted order by id,orderorder
open point1
fetch next from point1 into @id ,@orderorder,@name,@purview,@dabletime,@funtion,@personname
while @@fetch_status=0
begin
update point set orderorder=cast(orderorder as int)-1 where id=@id and orderorder>@orderorder
fetch next from point1 into @id,@orderorder,@name,@purview,@dabletime,@funtion,@personname
endclose point1
deallocate point1
select id,orderorder,name from point where id = 2 order by id,orderorder
id orderorder name
null null null
insert into point (id,orderorder,name) values ('2','1','1')
insert into point (id,orderorder,name) values ('2','2','2')
insert into point (id,orderorder,name) values ('2','3','3')
insert into point (id,orderorder,name) values ('2','4','4')
insert into point (id,orderorder,name) values ('2','5','5')
select id,orderorder,name from point where id = 2 order by id,orderorder
id orderorder name
2 1 1
2 2 2
2 3 3
2 4 4
2 5 5
delete from point where id = 2 and orderorder = 5(所影响的行数为 0 行)
(所影响的行数为 1 行)
select id,orderorder,name from point where id = 2 order by id,orderorder
id orderorder name
2 1 1
2 2 2
2 3 3
2 4 4
2 5 5
请问我从删除到显示(所影响的行数为 0 行)(所影响的行数为 1 行)的过程是怎样的??
create trigger t_delect on point
for delete
as
if exists(select 1 from point where id=2 and orderorder=5)
print '删除的记录还存在'
else
print '删除的记录不存在了'运行
delete from point where id = 2 and orderorder = 5结果
----------------------
删除的记录不存在了(所影响的行数为 1 行)
-----------------------信息先是显示trigger中的操作再是显示delete/update/insert的操作影响的行数
(所影响的行数为 1 行)这个先是显示trigger中游标循环
update操作所影响的行数,是0行再显示delete影响的行数,是1行
delete from point where id = 2 and orderorder = 5再运行触发器中的相关游标操作
有什么方法解决吗??