请问一个MSSQL触发器的有关问题
请教一个MSSQL触发器的问题
我有以下TB1表,
date字段取的是系统的当前的日期,但无法精确到秒,我想通过触发器当插入或更新时TB1,把date日期精确到秒并加到checkdate字段,请教各位,谢谢!
ID date checkdate
1 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
2 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
3 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
------解决方案--------------------
字段类型改为datetime,不用触发器。
------解决方案--------------------
我有以下TB1表,
date字段取的是系统的当前的日期,但无法精确到秒,我想通过触发器当插入或更新时TB1,把date日期精确到秒并加到checkdate字段,请教各位,谢谢!
ID date checkdate
1 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
2 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
3 2010-08-10 00:00:00.000 2010-08-10 10:10:10.200
------解决方案--------------------
字段类型改为datetime,不用触发器。
------解决方案--------------------
- SQL code
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([ID] int,[date] datetime,[checkdate] datetime) insert [test] select 1,'2010-08-10 00:00:00.000',null union all select 2,'2010-08-10 00:00:00.000',null union all select 3,'2010-08-10 00:00:00.000',null go if OBJECT_ID('tri_test')is not null drop trigger tro_test go create trigger tri_test on test for update as update test set [checkdate]=getdate() from inserted a where a.id=test.id --测试 update test set [date]='2012-03-21 00:00:00.000' where id=1 select * from test /* ID date checkdate 1 2012-03-21 00:00:00.000 2012-04-30 10:37:50.420 2 2010-08-10 00:00:00.000 NULL 3 2010-08-10 00:00:00.000 NULL */
------解决方案--------------------
- SQL code
--> 测试数据:[test] if object_id('[test]') is not null drop table [test] create table [test]([ID] int,[date] date,[checkdate] datetime) insert [test] select 1,'2010-08-10',null union all select 2,'2010-08-10',null union all select 3,'2010-08-10',null go if OBJECT_ID('tri_test')is not null drop trigger tro_test go create trigger tri_test on test for insert,update as update test set [checkdate]=getdate() from inserted a where a.id=test.id --测试 update test set [date]='2012-03-21 00:00:00.000' where id=1 insert test select 4,getdate(),null update test set [date]=getdate() select * from test /* ID date checkdate 1 2012-04-30 2012-04-30 10:42:19.343 2 2012-04-30 2012-04-30 10:42:19.343 3 2012-04-30 2012-04-30 10:42:19.343 4 2012-04-30 2012-04-30 10:42:19.343 */
------解决方案--------------------
1楼的方法最简单,顶一下。2、3楼也行。