34,587
社区成员
发帖
与我相关
我的任务
分享
create trigger triu_订购单
on 订购单
for insert,update
as
begin
declare @c numeric(18,4),@c1 numeric(18,4)
select @c=金额 from inserted
select @c1=sum(单价*数量) from 订单明细 m,inserted i where m.订单编号=i.订单编号
if @c<>@c1
rollback
end
----------------------------------------------------------------------------------
-- Author : htl258(Tony)
-- Date : 2010-05-15 18:33:15
-- Version: Microsoft SQL Server 2008 (RTM) - 10.0.1600.22 (Intel X86)
-- Jul 9 2008 14:43:34
-- Copyright (c) 1988-2008 Microsoft Corporation
-- Developer Edition on Windows NT 5.1 <X86> (Build 2600: Service Pack 2)
-- Blog : http://blog.csdn.net/htl258
----------------------------------------------------------------------------------
--> 生成测试数据表: [订购单]
IF OBJECT_ID('[订购单]') IS NOT NULL
DROP TABLE [订购单]
GO
CREATE TABLE [订购单] ([订单编号] [int],[金额] [int])
INSERT INTO [订购单]
SELECT '1','12'
--> 生成测试数据表: [订单明细]
IF OBJECT_ID('[订单明细]') IS NOT NULL
DROP TABLE [订单明细]
GO
CREATE TABLE [订单明细] ([订单编号] [int],[单价] [int],[数量] [int])
INSERT INTO [订单明细]
SELECT '1','3','4' UNION ALL
SELECT '2','5','8'
SELECT * FROM [订购单]
SELECT * FROM [订单明细]
-->SQL查询如下:
if object_id('f_getamount') is not null
drop function f_getamount
go
create function f_getamount
(@订单编号 int)
returns money
as
begin
return(select sum(单价*数量) from [订单明细] where [订单编号]=@订单编号)
end
go
--创建约束:
alter table [订购单] add check([金额]=dbo.f_getamount([订单编号]))
go
insert [订购单] select 2,12
/*
消息 547,级别 16,状态 0,第 1 行
INSERT 语句与 CHECK 约束"CK__订购单__3F1C4B12"冲突。该冲突发生于数据库"mydb",表"dbo.订购单"。
语句已终止。
*/
insert [订购单] select 2,40
/*
(1 行受影响)
*/
select * from [订购单]
/*
订单编号 金额
----------- -----------
1 12
2 40
(2 行受影响)
*/
用约束的方法