create table a(p1 int,p2 datetime)select identity(int,1,1)as id ,a.* into b from acreate trigger cx on b for insert,update
as
update b set p2=getdate()
where id in (select id from inserted)
goinsert into b (p1)
select 1select * from binsert into b (p1)
select 1select * from bupdate b
set p1=2select * from bid p1 p2
----------- ----------- ------------------------------------------------------
1 1 2005-12-13 19:36:07.140(所影响的行数为 1 行)
id p1 p2
----------- ----------- ------------------------------------------------------
1 1 2005-12-13 19:36:07.140
2 1 2005-12-13 19:37:11.187(所影响的行数为 2 行)
id p1 p2
----------- ----------- ------------------------------------------------------
1 2 2005-12-13 19:37:36.483
2 2 2005-12-13 19:37:36.483(所影响的行数为 2 行)
as
update b set p2=getdate()
where id in (select id from inserted)
goinsert into b (p1)
select 1select * from binsert into b (p1)
select 1select * from bupdate b
set p1=2select * from bid p1 p2
----------- ----------- ------------------------------------------------------
1 1 2005-12-13 19:36:07.140(所影响的行数为 1 行)
id p1 p2
----------- ----------- ------------------------------------------------------
1 1 2005-12-13 19:36:07.140
2 1 2005-12-13 19:37:11.187(所影响的行数为 2 行)
id p1 p2
----------- ----------- ------------------------------------------------------
1 2 2005-12-13 19:37:36.483
2 2 2005-12-13 19:37:36.483(所影响的行数为 2 行)
解决方案 »
- SQL安装问题
- 难题哦,有兴趣的来试试。
- sql 拆分字符串
- 如何用SQL语句得到文件的日期
- SQL中 打开Access数据库出错当(Opendatasource)
- 服务器: 消息 8114,级别 16,状态 5,过程 InsertMoniterValues,行 14 将数据类型 varchar 转换为 real 时出错。
- 为什么我添加的数据有的为空白,而有的为NULL?
- select
- 怎么修改库的默认排序规则?
- Sql Server2000数据导入的问题!
- 如何按日期为某一字段赋值?
- 更改条件再新开一贴,求一SQL语句!急,解决马上揭贴!!!主要是连续问题,不知如何写SQL语句? 只有20分了!原贴http://community.csdn.net
create trigger cx on b for update
as
if update(p1)
begin
update b set p2=getdate()
where id in (select id from inserted)
end
create trigger cx1 on b for insert
as
if (select p1 from inserted ) is not null
begin
update b set p2=getdate()
where id in (select id from inserted)
endinsert into b (p0)
select 1select * from binsert into b (p1)
select 1select * from bupdate b
set p0=2select * from bupdate b
set p1=2select * from bid p0 p1 p2
----------- ----------- ----------- ------------------------------------------------------
1 1 NULL NULL(所影响的行数为 1 行)id p0 p1 p2
----------- ----------- ----------- ------------------------------------------------------
1 1 NULL NULL
2 NULL 1 2005-12-13 20:49:08.890(所影响的行数为 2 行)id p0 p1 p2
----------- ----------- ----------- ------------------------------------------------------
1 2 NULL NULL
2 2 1 2005-12-13 20:49:08.890(所影响的行数为 2 行)id p0 p1 p2
----------- ----------- ----------- ------------------------------------------------------
1 2 2 2005-12-13 20:51:35.390
2 2 2 2005-12-13 20:51:35.390(所影响的行数为 2 行)
把相应的时间字段的默认值设为 getdate(),
这样当触发了某动作时,比如,插入,时间字段会自动获得当前系统时间.
若过是修改,则再添加相应的触发器,时间字段用update, getdate().我说的和楼上的楼上的...的是一样的.