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
*/