我的表的结构是这样的
CREATE TABLE [dbo].[院内存款] (
[凭证号] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[姓名] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[日期] [datetime] NOT NULL ,
[存入] [numeric](10, 2) NULL ,
[支出] [numeric](10, 2) NULL ,
[利息] [numeric](10, 2) NULL
) ON [PRIMARY]
请问怎样实现名字相同的记录,存入-支出不能小于零?
CREATE TABLE [dbo].[院内存款] (
[凭证号] [varchar] (16) COLLATE Chinese_PRC_CI_AS NULL ,
[姓名] [varchar] (16) COLLATE Chinese_PRC_CI_AS NOT NULL ,
[日期] [datetime] NOT NULL ,
[存入] [numeric](10, 2) NULL ,
[支出] [numeric](10, 2) NULL ,
[利息] [numeric](10, 2) NULL
) ON [PRIMARY]
请问怎样实现名字相同的记录,存入-支出不能小于零?
alter table [院内存款] add CONSTRAINT con_name check([存入]-[支出])>=0)
消息 547,级别 16,状态 0,第 2 行
INSERT 语句与 CHECK 约束"con_name"冲突。该冲突发生于数据库"test",表"dbo.院内存款"。
语句已终止。
--请问怎样实现名字相同的记录,存入-支出不能小于零?create trigger tri_t on t
after insert
as
begin
if exists(select 1 from inserted
where name in(select distinct name from t)
and 存入-支出<0)
begin
rollback
print '存入-支出,不能小于0!'
end
end
[code=SQL]
--请问怎样实现名字相同的记录,存入-支出不能小于零?create trigger tri_t on t
after insert
as
begin
if exists((select name,count(1) from t group by name having count(*)>1)
and
(select 1 from inserted where 存入-支出<0))
begin
rollback
print '存入-支出,不能小于0!'
end
end[/code]
create table t
(name varchar(10),[in] int, [out] int)insert into t
select 'zhang',10,8select * from t
create trigger tri_t on t
after insert
as
begin
if exists(select name,count(1) from t group by name having count(*)>1)
begin
if exists (select 1 from inserted where [in]-[out]<0)
begin
rollback
print '存入-支出,不能小于0!'
end
end
end
insert into t
select 'zhang',15,20/*
存入-支出,不能小于0!
*/insert into t
select 'liang',10,12select * from t/*
name in out
---------- ----------- -----------
zhang 10 8
liang 10 12(所影响的行数为 2 行)
*/
请问inserted 是什么意思?
虚表
虚表,它保存刚插入的数据