根据SQL Server 触发器 对于DML操作,只有一次触发,不管 你的语句有多少行,只运行最后一行 的 规定,
我总怀疑:这个触发器代码,有问题,没有完全被执行。
请高手帮帮忙,将我的触发器代码,改成“能够处理多行的触发器”也就是说,通过改写连接方式,将inserted表 和 deleted 表 或者基础表 进行连接,以便 获得 DML语句操作的 全部行。源代码为:
USE [date]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[prictTableTrigger]
ON [dbo].[priceTable]
AFTER INSERT,update
AS
BEGIN
SET NOCOUNT ON;
declare @todaypriceid int
declare @avgprice float
declare @icount int
declare @price float
declare @uid varchar(50)
declare @i int
declare @id int
select @todaypriceid=todaypriceid from inserted
select @icount=count(*) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
declare ptcursor SCROLL cursor for select price,uid from priceTable where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
open ptcursor
if @icount=1
begin
fetch next from ptcursor into @price,@uid
set @avgprice=@price
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,0,@uid,1,getdate())
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
else
begin
if @icount>1 and @icount<5
select @avgprice=avg(price) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
else
begin
set @i=1
set @avgprice=0
fetch next from ptcursor into @price,@uid
while @@fetch_status=0
begin
if @i<@icount and @i>1
begin
set @avgprice=@avgprice+@price
end
set @i=@i+1
fetch next from ptcursor into @price,@uid
end
set @avgprice=@avgprice/(@icount-2)
end
delete from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0
set @avgprice=round(convert(numeric(8,2),@avgprice),0)
fetch first from ptcursor into @price,@uid
while @@fetch_status=0
begin
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,@price-@avgprice,@uid,null,getdate())
fetch next from ptcursor into @price,@uid
end
declare rocursor cursor for select ID from ReportOrder where Differences<=0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences desc
set @i=1
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
declare rocursor cursor for select ID from ReportOrder where Differences>0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
select top 1 @uid=uid from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by ord
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
close ptcursor
DEALLOCATE ptcursor
END
我总怀疑:这个触发器代码,有问题,没有完全被执行。
请高手帮帮忙,将我的触发器代码,改成“能够处理多行的触发器”也就是说,通过改写连接方式,将inserted表 和 deleted 表 或者基础表 进行连接,以便 获得 DML语句操作的 全部行。源代码为:
USE [date]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[prictTableTrigger]
ON [dbo].[priceTable]
AFTER INSERT,update
AS
BEGIN
SET NOCOUNT ON;
declare @todaypriceid int
declare @avgprice float
declare @icount int
declare @price float
declare @uid varchar(50)
declare @i int
declare @id int
select @todaypriceid=todaypriceid from inserted
select @icount=count(*) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
declare ptcursor SCROLL cursor for select price,uid from priceTable where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
open ptcursor
if @icount=1
begin
fetch next from ptcursor into @price,@uid
set @avgprice=@price
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,0,@uid,1,getdate())
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
else
begin
if @icount>1 and @icount<5
select @avgprice=avg(price) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
else
begin
set @i=1
set @avgprice=0
fetch next from ptcursor into @price,@uid
while @@fetch_status=0
begin
if @i<@icount and @i>1
begin
set @avgprice=@avgprice+@price
end
set @i=@i+1
fetch next from ptcursor into @price,@uid
end
set @avgprice=@avgprice/(@icount-2)
end
delete from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0
set @avgprice=round(convert(numeric(8,2),@avgprice),0)
fetch first from ptcursor into @price,@uid
while @@fetch_status=0
begin
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,@price-@avgprice,@uid,null,getdate())
fetch next from ptcursor into @price,@uid
end
declare rocursor cursor for select ID from ReportOrder where Differences<=0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences desc
set @i=1
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
declare rocursor cursor for select ID from ReportOrder where Differences>0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
select top 1 @uid=uid from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by ord
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
close ptcursor
DEALLOCATE ptcursor
END
inserted表 和 deleted 表也是可以连接的
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[prictTableTrigger]
ON [dbo].[priceTable]
AFTER INSERT,update
AS
BEGIN
SET NOCOUNT ON;
declare @todaypriceid int
declare @avgprice float
declare @icount int
declare @price float
declare @uid varchar(50)
declare @i int
declare @id int
declare cur_insert cursor for select todaypriceid from inserted
open cur_insert
fetch next from cur_insert into @todaypriceid
while @@fetch_status=0
begin
select @todaypriceid=todaypriceid from inserted
select @icount=count(*) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
declare ptcursor SCROLL cursor for select price,uid from priceTable where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
open ptcursor
if @icount=1
begin
fetch next from ptcursor into @price,@uid
set @avgprice=@price
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,0,@uid,1,getdate())
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
else
begin
if @icount>1 and @icount<5
select @avgprice=avg(price) from [priceTable] where todaypriceid=@todaypriceid and datediff(d,dtdate,getdate())<=2
else
begin
set @i=1
set @avgprice=0
fetch next from ptcursor into @price,@uid
while @@fetch_status=0
begin
if @i<@icount and @i>1
begin
set @avgprice=@avgprice+@price
end
set @i=@i+1
fetch next from ptcursor into @price,@uid
end
set @avgprice=@avgprice/(@icount-2)
end
delete from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0
set @avgprice=round(convert(numeric(8,2),@avgprice),0)
fetch first from ptcursor into @price,@uid
while @@fetch_status=0
begin
insert into ReportOrder(tid,price,Differences,uid,ord,dtdate) values(@todaypriceid,@price,@price-@avgprice,@uid,null,getdate())
fetch next from ptcursor into @price,@uid
end
declare rocursor cursor for select ID from ReportOrder where Differences<=0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences desc
set @i=1
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
declare rocursor cursor for select ID from ReportOrder where Differences>0 and tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by Differences
open rocursor
fetch next from rocursor into @id
while @@fetch_status=0
begin
update ReportOrder set ord=@i where ID=@id
set @i=@i+1
fetch next from rocursor into @id
end
close rocursor
DEALLOCATE rocursor
select top 1 @uid=uid from ReportOrder where tid=@todaypriceid and datediff(d,dtdate,getdate())=0 order by ord
update price_type set guapj=@avgprice,decldate=getdate(),uid=@uid where id=@todaypriceid
end
close ptcursor
DEALLOCATE ptcursor
fetch next from cur_insert into @todaypriceid
end
close cur_insert
DEALLOCATE cur_insert
END
尊敬的:xys_777! 您真是高水平啊!!! 我真是:佩服得 五体投地!
尊敬的xys_777:您的代码,已经用上了。出现的新问题是:CUP 占得特别大,速度特别慢。会不会是:游标逐行处理 DML 语句的原因。如果是的话,请改掉!谢谢!!!
1、SQL ERVER的触发器,每次只能触发一段的论述,所以,一个触发器貌似运行了,实际上并没有把全部代码运行,出现了“陷阱”;
2、于是,有人为了回避这一陷阱,又用游标的办法逐行处理 DML 语句。结果导致 服务器运行性能下降。 于是,我学习改为“多行运行触发器”,但是,我是菜鸟,没有那个本事;于是就在网上求助高手: 多亏xys_77帮忙,搞通了,数据完整性的问题解决了。衷心感谢啊! 现在运行速度很慢,我怀疑:是不是原来代码就用了“游标”的办法,请求 xys_77,再帮个忙,修改一下。 我也是万分着急。