我建立了一个Trigger,内容如下:
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Change Company ID
ALTER TRIGGER [PCALLOC_UPDATE]
ON [dbo].[PC433100] --Change 31 to your companyid
AFTER insert ,UPDATE,delete
AS declare
@IsInsert bit,
@IsUpdate bit,
@Isdelete bit
if exists(select 1 from inserted) and not exists (select 1 from deleted)
SET @IsInsert = 1
ELSE
SET @IsInsert = 0IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsUpdate = 1
ELSE
SET @IsUpdate = 0IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsDelete = 1
ELSE
SET @IsDelete = 0
BEGIN
if @IsUpdate =1
update Z_pcalloc3100 set ponumber=a.pc43001,grnnumber=isnull(a.pc43041,''),invoiceno=isnull(a.pc43044,''),ORDERLINENO=a.pc43002,PODELLINENO=a.pc43004,
batchnumber=isnull(a.pc43005,''),batchid=isnull(a.sc33003,''),itemcode=a.pc03005,qty=a.pc43007,cost=a.pc43014,currency=a.pc43040
from (select pc43001,pc43041,pc43002,pc43004,pc43005,pc43044,sc33003,pc03005,pc43007,pc43014,pc43011,pc43040 from pc433100
left join pc033100 on pc43001=pc03001 and pc43002=pc03002
left join sc333100 on pc03005=sc33001 and pc43005=sc33003 and pc43001=sc33019) a
where ponumber=a.pc43001 and orderlineno=a.pc43002 and PODELLINENO=a.pc43004 and
batchnumber=a.pc43005 and itemcode=a.pc03005
else
if @IsInsert=1
insert into Z_pcalloc3100 (ponumber,grnnumber,invoiceno,orderlineno,podellineno,batchnumber,batchid,itemcode,itemname,qty,cost,company,flagalloc,flagstats,currency,users,allocdate)
select pc43001,isnull(pc43041,''),isnull(pc43044,''),pc43002,pc43004,pc43005,isnull(sc33003,''),pc03005,isnull(sc04004,''),isnull(pc43007,''),isnull(pc43014,''),31,0,0,isnull(pc43040,''),'','' from pc433100
left join pc033100 on pc43001=pc03001 and pc43002=pc03002
left join sc333100 on pc03005=sc33001 and pc43005=sc33003 and pc43001=sc33019
left join sc043100 on sc33001=sc04001
where pc43001=(select pc43001 from inserted) and pc43041=(select pc43041 from inserted) and
pc43002=(select pc43002 from inserted) and pc43004=(select pc43004 from inserted)
else
if @IsDelete = 1
delete from Z_pcalloc3100 where ponumber=(select pc43001 from deleted) and
orderlineno=(select pc43002 from deleted) and podellineno=(select pc43004 from deleted) and
batchnumber=(select pc43005 from deleted) SET NOCOUNT ON;END
最近我的ERP系统经常发生如下错误
Data File: PC433100
Error Description:
Can't update table.其中PC433100就是我建立Trigger的Table,报错的原因应该是当ERP试图去create或者update表PC433100的时候,发现被占用了。但是,我觉得PC433100这张表应该不会被Trigger占用,请帮忙看看是否是trigger本身的问题?
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
--Change Company ID
ALTER TRIGGER [PCALLOC_UPDATE]
ON [dbo].[PC433100] --Change 31 to your companyid
AFTER insert ,UPDATE,delete
AS declare
@IsInsert bit,
@IsUpdate bit,
@Isdelete bit
if exists(select 1 from inserted) and not exists (select 1 from deleted)
SET @IsInsert = 1
ELSE
SET @IsInsert = 0IF EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsUpdate = 1
ELSE
SET @IsUpdate = 0IF NOT EXISTS(SELECT 1 FROM inserted) AND EXISTS(SELECT 1 FROM deleted)
SET @IsDelete = 1
ELSE
SET @IsDelete = 0
BEGIN
if @IsUpdate =1
update Z_pcalloc3100 set ponumber=a.pc43001,grnnumber=isnull(a.pc43041,''),invoiceno=isnull(a.pc43044,''),ORDERLINENO=a.pc43002,PODELLINENO=a.pc43004,
batchnumber=isnull(a.pc43005,''),batchid=isnull(a.sc33003,''),itemcode=a.pc03005,qty=a.pc43007,cost=a.pc43014,currency=a.pc43040
from (select pc43001,pc43041,pc43002,pc43004,pc43005,pc43044,sc33003,pc03005,pc43007,pc43014,pc43011,pc43040 from pc433100
left join pc033100 on pc43001=pc03001 and pc43002=pc03002
left join sc333100 on pc03005=sc33001 and pc43005=sc33003 and pc43001=sc33019) a
where ponumber=a.pc43001 and orderlineno=a.pc43002 and PODELLINENO=a.pc43004 and
batchnumber=a.pc43005 and itemcode=a.pc03005
else
if @IsInsert=1
insert into Z_pcalloc3100 (ponumber,grnnumber,invoiceno,orderlineno,podellineno,batchnumber,batchid,itemcode,itemname,qty,cost,company,flagalloc,flagstats,currency,users,allocdate)
select pc43001,isnull(pc43041,''),isnull(pc43044,''),pc43002,pc43004,pc43005,isnull(sc33003,''),pc03005,isnull(sc04004,''),isnull(pc43007,''),isnull(pc43014,''),31,0,0,isnull(pc43040,''),'','' from pc433100
left join pc033100 on pc43001=pc03001 and pc43002=pc03002
left join sc333100 on pc03005=sc33001 and pc43005=sc33003 and pc43001=sc33019
left join sc043100 on sc33001=sc04001
where pc43001=(select pc43001 from inserted) and pc43041=(select pc43041 from inserted) and
pc43002=(select pc43002 from inserted) and pc43004=(select pc43004 from inserted)
else
if @IsDelete = 1
delete from Z_pcalloc3100 where ponumber=(select pc43001 from deleted) and
orderlineno=(select pc43002 from deleted) and podellineno=(select pc43004 from deleted) and
batchnumber=(select pc43005 from deleted) SET NOCOUNT ON;END
最近我的ERP系统经常发生如下错误
Data File: PC433100
Error Description:
Can't update table.其中PC433100就是我建立Trigger的Table,报错的原因应该是当ERP试图去create或者update表PC433100的时候,发现被占用了。但是,我觉得PC433100这张表应该不会被Trigger占用,请帮忙看看是否是trigger本身的问题?
..
delete from Z_pcalloc3100 where ponumber=(select pc43001 from deleted) and
orderlineno=(select pc43002 from deleted) and podellineno=(select pc43004 from deleted) and
batchnumber=(select pc43005 from deleted)
..
==>delete a
from Z_pcalloc3100 a
join deleted d on a.ponumber=d.pc43001
and a.orderlineno=d.pc43002
and a.podellineno=d.pc43004
and a.batchnumber=d.pc43005
为什么要这样改?
delete a
from Z_pcalloc3100 a
join deleted d on a.ponumber=d.pc43001
and a.orderlineno=d.pc43002
and a.podellineno=d.pc43004
and a.batchnumber=d.pc43005