34,830
社区成员




- Create table aa(id int identity,oid int,date datetime)
- alter trigger Insert_aa on aa
- instead of insert
- as
- begin
- if not exists(select 1 from inserted a where exists(select 1 from aa where a.oid=oid
- and convert(varchar(10),a.date,120)=convert(varchar(10),date,120)))
- begin
- insert into aa(oid,date) select oid ,date from inserted
- end
- end
- insert into aa select 12,getdate()
- insert into aa select 12,getdate()
- insert into aa select 12,getdate()
- insert into aa select 12,getdate()
- select * from aa
- id oid date
- ----------- ----------- -----------------------
- 1 12 2007-10-19 00:00:00.000
- if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[t]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
- drop table [dbo].[t]
- GO
- CREATE TABLE [dbo].[t] (
- [dttime] [datetime] NOT NULL,
- dt as convert(varchar(10),dttime ,120),
- CONSTRAINT [IX_T] UNIQUE
- (
- dt ASC
- )
- ) ON [PRIMARY]
- insert t select getdate() --可以成功插入
- insert t select getdate() --被dt的唯一值要求所约束
- select * from t --结果,只插入了一条记录
- /*
- 违反了 UNIQUE KEY 约束 'IX_T'。不能在对象 't' 中插入重复键。
- 语句已终止。
- dttime dt
- ------------------------------------------------------ ----------
- 2007-10-18 21:37:03.373 2007-10-18
- */