//触发器存在则删除if (object_id('T_addNotice', 'tr') is not null)
drop trigger T_addNotice
go
createtrigger T_addNotice
on notice
forinsert --插入触发可选delete,updateas
---处理代码
update notice set
--获取序列号如果当日获取结果为空则值置1否则加1
SerialNum=
(selectcasewhen (select SerialNum from notice where AddTime=(selectmax(AddTime) from notice where AddTime>(selectcast(convert(varchar(10),getdate(),120)+' 00:00:00'as datetime)) and ExtendedCode isnotnulland SerialNum isnotnull))
isnullthen1else
(1+(select SerialNum from notice where AddTime=(selectmax(AddTime) from notice where AddTime>(selectcast(convert(varchar(10),getdate(),120)+' 00:00:00'as datetime)) and ExtendedCode isnotnulland SerialNum isnotnull))
)
end
),
--获取扩展码100000截取后5位位扩展码
ExtendedCode=(selectright(cast(100000+(select ExtendedCode from notice where AddTime=(selectmax(AddTime) from notice where ExtendedCode isnotnulland SerialNum isnotnull))+1asvarchar),5))
where Id=(select Id from notice where AddTime=(selectmax(AddTime) from notice));
go