create trigger b名 on b表 for insert,update
as
begin
declare @a table (a varchar(100),b int)
insert @a select 提單號,sum(件數) 件數 from b表 where 提單號 in (select 提單號 from inserted)
if exists(select 1 from @a _a join A表 _b on _a.提單號=_b.提單號 and _a.件數<_b.件數)
begin
rollback tran
raiserror('错误...',16,3)
end
end
as
begin
declare @a table (a varchar(100),b int)
insert @a select 提單號,sum(件數) 件數 from b表 where 提單號 in (select 提單號 from inserted)
if exists(select 1 from @a _a join A表 _b on _a.提單號=_b.提單號 and _a.件數<_b.件數)
begin
rollback tran
raiserror('错误...',16,3)
end
end
as
begin
declare @a table (提單號 varchar(100),件數 int,重量 numeric(18,2))
insert @a select 提單號,sum(件數) 件數,sum(重量) 重量 from b表 where 提單號 in (select 提單號 from inserted)
if exists(select 1 from @a _a join A表 _b on _a.提單號=_b.提單號 and (_a.件數<_b.件數 or _a.重量<_b.重量))
begin
rollback tran
raiserror('错误...',16,3)
end
end
as
begin
declare @a table (billnumber nvarchar(100),GoodsCount int,GoodsWeight numeric(18,2))
insert @a select billnumber,sum(GoodsCount) GoodsCount,sum(GoodsWeight) GoodsWeight from tblCustomsClearance where billnumber in (select billnumber from inserted)
if exists(select 1 from @a _a join tblDataMaster _b on _a.billnumber=_b.billnumber and (_a.GoodsCount<_b.GoodsCount or _a.GoodsWeight<_b.GoodsWeight))
begin
rollback tran
raiserror('错误...',16,3)
end
end樓上的,我按照你的寫法修改成我的要求,但是用語法檢查通不過~
提示:
列 'tblCustomsClearance.BillNumber' 在选择列表中无效,因为该列未包含在聚合函数中,并且没有 GROUP BY 子句。也就是在這一行:insert @a select ......
as
begin
declare @a table (billnumber nvarchar(100),GoodsCount int,GoodsWeight numeric(18,2))
insert @a select billnumber,sum(GoodsCount),sum(GoodsWeight) from tblCustomsClearance where billnumber in (select billnumber from inserted) group by billnumber
if exists(select 1 from @a _a join tblDataMaster _b on _a.billnumber=_b.billnumber and (_a.GoodsCount<_b.GoodsCount or _a.GoodsWeight<_b.GoodsWeight))
begin
rollback tran
raiserror('错误...',16,3)
end
end
CREATE TABLE [dbo].[tblDataMaster] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[CollectGoodsDate] [smalldatetime] NULL ,
[BillNumber] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[FromAddr] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[DestAddr] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[GoodsName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[GoodsCount] [int] NULL ,
[GoodsWeight] [float] NULL ,
[CuNo] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CollectMan] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[PayMent] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Freight] [float] NULL ,
[Cost] [float] NULL ,
[AdditionalCharge] [float] NULL ,
[Receipt] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[CollectionOnDelivery] [float] NULL ,
[Re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[Typer] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL ,
[TypeDate] [smalldatetime] NULL ,
[Auditing] [bit] NULL
) ON [PRIMARY]
GOCREATE TABLE [dbo].[tblCustomsClearance] (
[ID] [int] IDENTITY (1, 1) NOT NULL ,
[BillNumber] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[ExportDate] [smalldatetime] NOT NULL ,
[CraftBrotherName] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[GoodsCount] [int] NULL ,
[GoodsWeight] [float] NULL ,
[Re] [nvarchar] (50) COLLATE Chinese_PRC_CI_AS NULL
) ON [PRIMARY]
GO
as
begin
declare @a table (billnumber nvarchar(100),GoodsCount int,GoodsWeight numeric(18,2))
insert @a select billnumber,sum(GoodsCount),sum(GoodsWeight) from tblCustomsClearance where billnumber in (select billnumber from inserted) group by billnumber
if exists(select 1 from @a _a join tblDataMaster _b on _a.billnumber=_b.billnumber and (_a.GoodsCount>_b.GoodsCount or _a.GoodsWeight>_b.GoodsWeight))
begin
rollback tran
raiserror('错误...',16,3)
end
end