以下是某触发器代码
CREATE TRIGGER aa ON dbo.Indent_hj
for insert,update
AS IF EXISTS(SELECT * FROM inserted)
BEGIN
if exists (select* from deleted)
begin
rollback
endelse
begin当插入某条新数据时,如果发现该数据的orddate值=表中某条旧数据的orddate值,则按如下规则将该数据替换掉表中旧数据:Number字段的值仍采用旧数据的,其他字段均更新为新数据的。
end
end
CREATE TRIGGER aa ON dbo.Indent_hj
for insert,update
AS IF EXISTS(SELECT * FROM inserted)
BEGIN
if exists (select* from deleted)
begin
rollback
endelse
begin当插入某条新数据时,如果发现该数据的orddate值=表中某条旧数据的orddate值,则按如下规则将该数据替换掉表中旧数据:Number字段的值仍采用旧数据的,其他字段均更新为新数据的。
end
end
Number Kind office orddate
09DBX12-100 普通 23 2009-12-1 下午 02:45:43当插入如下新数据:
Number Kind office orddate
09DBX12-1 特别 34 2009-12-1 下午 02:45:43发现原表中有相同orddate的数据,则将旧数据替换掉,但是Number值不变,替换结果:
Number Kind office orddate
09DBX12-100 特别 34 2009-12-1 下午 02:45:43
CREATE TRIGGER aa ON dbo.Indent_hj
for insert,update
AS IF EXISTS(SELECT * FROM inserted)
BEGIN
if exists (select* from deleted)
begin
rollback
end
else
begin
update dbo.Indent_hj set Kind=i.Kind,office=i.office
from inserted i where orddate=i.orddateend
end
instead of insert
AS
begin
declare @Number varchar(50), @Kind varchar(10), @office int, @orddate datetime
declare cur cursor for select * from inserted
open cur
fetch cur into @Number ,@Kind ,@office ,@orddate
while @@fetch_status=0
begin
if exists(select * from Indent_hj where orddate=@orddate)
update Indent_hj set Number=@Number, Kind =@Kind,office=@office where orddate=@orddate
else
insert Indent_hj (Number, Kind, office, orddate) values(@Number ,@Kind ,@office ,@orddate)
fetch cur into @Number ,@Kind ,@office ,@orddate
end
close cur
deallocate cur
end
on [dbo].[T_UrlType]
after Insert
as
---新增---
IF EXISTS(SELECT 1 FROM inserted) AND NOT EXISTS(SELECT 1 FROM deleted)
begin
update dbo.Indent_hj set Kind=i.Kind,office=i.office
from inserted i where orddate=i.orddate
end
end
实际的每条数据有30多列,如果都写成Kind=i.Kind,office=i.office这样的话不太合适吧...
Number Kind office orddate
09DBX12-100 特别 34 2009-12-1 下午 02:45:43由于触发器的插入操作中还有其他一些语句,所以不太方便修改整个触发器