现有触发器 插入 更新 A表 如果B表 有相关数据则更新,负责就插入数据这个触发器是没问题的,可以正常运行。但如果我要批量插入 或者 批量更新时候 就出问题了。有人说用游标可以做这个 但 做个简单测试 --
-- Declare @ID int
--
--
-- Declare myCur CURSOR FOR
-- select ID from deleted
-- open myCur
--
-- fetch next from myCur into @ID
--
-- while @@fetch_status=0 BEGIN insert into T2(ID,H,iID)
select i.ID,i.H,i.ID
from inserted i --fetch next from myCur into @ID End-- Close myCur
-- DEALLOCATE myCur
--Insert statements for trigger here这个是 T1 数据变化 向T2 插入数据,用上游标 反而执行不了。 不知道改如何解决,现在不用游标却可以。但我说的那个和这个 相似 却不能
-- Declare @ID int
--
--
-- Declare myCur CURSOR FOR
-- select ID from deleted
-- open myCur
--
-- fetch next from myCur into @ID
--
-- while @@fetch_status=0 BEGIN insert into T2(ID,H,iID)
select i.ID,i.H,i.ID
from inserted i --fetch next from myCur into @ID End-- Close myCur
-- DEALLOCATE myCur
--Insert statements for trigger here这个是 T1 数据变化 向T2 插入数据,用上游标 反而执行不了。 不知道改如何解决,现在不用游标却可以。但我说的那个和这个 相似 却不能
ON [dbo].[GoodsAudit]
after UPDATE--,INSERT
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON; -- Insert statements for trigger here
Declare @GoodsID nvarchar(100)
Declare @ReserID nvarchar(100) set @ReserID=(select D.resellerId From inserted D) Set @GoodsID=(Select count(*) From [Goods] where ID in (select D.GoodsID From inserted D) and resellerId=@ReserID)
if(@GoodsID<=0) BEGIN
insert into [Goods]
(ID,resellerId,[wineId],foreignName,chineseName,wineLogo,
wineVariety,country,area,specification,price,[year],[content],
recommend,hitCount,productCode,barCode,PromotionalDescription,
state,AuditState,createdTime,createdBy,modifiedTime,modifiedBy,
MaxPrice,MinPrice,WineURl,WineName)
select i.GoodsID,i.resellerId,i.wineId,i.foreignName,i.chineseName,
i.wineLogo,i.wineVariety,i.country,i.area,i.specification,i.price,i.[year],
i.[content],i.recommend,i.hitCount,i.productCode,i.barCode,i.PromotionalDescription,
'1',i.state,i.createdTime,i.createdBy,i.modifiedTime,i.modifiedBy,
i.MaxPrice,i.MinPrice,i.WineURl,i.WineName From inserted i where i.GoodsID not in (select ID From [Goods]) And @ReserID=i.ResellerID and i.[state]=2
END else
BEGIN update [Goods] set ID=i.GoodsID, ResellerID=i.ResellerID,[wineId]=i.[wineId],foreignName=i.foreignName,
chineseName=i.chineseName,wineLogo=i.wineLogo,wineVariety=i.wineVariety,
country=i.country,area=i.area,specification=i.specification,price=i.price,
[year]=i.[year],
[content]=i.[content],recommend=i.recommend,hitCount=i.hitCount,
productCode=i.productCode,barCode=i.barCode,
PromotionalDescription=i.PromotionalDescription,state='1',AuditState=i.state, createdTime=i.createdTime,createdBy=i.createdBy,
modifiedTime=i.modifiedTime,modifiedBy=i.modifiedBy,
MaxPrice=i.MaxPrice,MinPrice=i.MinPrice,
WineURl=i.WineURl,WineName=i.WineName From [Goods] G, inserted i
where G.ID=i.goodsId And G.ResellerID=i.ResellerID and i.[state]=2
END
ENDupdate [GoodsAudit] set state='2'消息 512,级别 16,状态 1,过程 insertUnmatchedWine,第 26 行
子查询返回的值不止一个。当子查询跟随在 =、!=、<、<=、>、>= 之后,或子查询用作表达式时,这种情况是不允许的。
语句已终止。update T1 set H='AAAA'可以执行成功不知道 该如何处理下 求助中 (ˇˍˇ)
Declare @GoodsID nvarchar(100) Declare @ReserID nvarchar(100) set @ReserID=(select D.resellerId From inserted D) Set @GoodsID=(Select count(*) From [Goods] where ID in (select D.GoodsID From inserted D) and resellerId=@ReserID) 如果影响的条数为多条记录,那么可能就无法返回正确的结果了。