如何能够获取数据库中每个表的最后一次数据被修改时间？
如何能够获取数据库中每个表的最后一次数据被修改时间？
[解决办法]
关注。
[解决办法]
在过程中加个getdate()
[解决办法]
不会 关注```
[解决办法]
--建立记录时间表
if object_id( 'RecordLastDate ') is not null drop table RecordLastDate
go
create table RecordLastDate(tbname varchar(50),lastdate datetime)
insert into RecordLastDate select name,null from sysobjects where type= 'u ' and status > =0 and name <> 'RecordLastDate '
go
--建立用户表
if object_id( 'tb_test ') is not null drop table tb_test
go
create table tb_test(c1 varchar(10))
go
--建立触发器
if object_id( 'tr_1 ') is not null drop trigger tr_1
go
create trigger tr_1 on tb_test
for insert,delete
as
if exists(select 1 from inserted) or exists(select 1 from deleted)
update RecordLastDate set lastdate=getdate() where tbname= 'tb_test '
go
--测试插入记录
insert tb_test values( 'abc ')
insert tb_test values( 'def ')
--查看时间记录表
select * from RecordLastDate where tbname= 'tb_test '
go
waitfor delay '00:00:05 '
go
--测试删除记录
insert tb_test values( 'def ')
--查看时间记录表
select * from RecordLastDate where tbname= 'tb_test '
go
--查看结果
/*
tbnamelastdate
-----------------------------------------------
tb_test2007-01-18 15:25:37.107
tbnamelastdate
-----------------------------------------------
tb_test2007-01-18 15:25:42.123
*/
[解决办法]
--建立记录时间表
if object_id( 'RecordLastDate ') is not null drop table RecordLastDate
go
create table RecordLastDate(tbname varchar(50),lastdate datetime)
insert into RecordLastDate select name,null from sysobjects where type= 'u ' and status > =0 and name <> 'RecordLastDate '
go
--建立用户表
if object_id( 'tb_test ') is not null drop table tb_test
go
create table tb_test(c1 varchar(10))
go
--建立触发器
if object_id( 'tr_1 ') is not null drop trigger tr_1
go
create trigger tr_1 on tb_test
for insert,delete
as
if exists(select 1 from inserted) or exists(select 1 from deleted)
update RecordLastDate set lastdate=getdate() where tbname= 'tb_test '
go
--测试插入记录
insert tb_test values( 'abc ')
insert tb_test values( 'def ')
--查看时间记录表
select * from RecordLastDate where tbname= 'tb_test '
go
waitfor delay '00:00:05 '
go
--测试删除记录
delete tb_test where c1= 'def '
--查看时间记录表
select * from RecordLastDate where tbname= 'tb_test '
go
--查看结果
/*
tbnamelastdate
-----------------------------------------------
tb_test2007-01-18 15:31:28.077
tbnamelastdate
-----------------------------------------------
tb_test2007-01-18 15:31:33.107
*/
[解决办法]
学习
[解决办法]
--触发器改一下，加update使修改记录也可以更新记录表
create trigger tr_1 on tb_test
for update,insert,delete
as
if exists(select 1 from inserted) or exists(select 1 from deleted)
update RecordLastDate set lastdate=getdate() where tbname= 'tb_test '
go
[解决办法]
也只好建立触发器，记录被更新的时间了。
[解决办法]
gz
