假设我创建了一张表A,用来存放数据库中其他用户表的相关信息,假设字段为表名称,创建时间,所有者. 如何实现当在数据库中创建一张表B时,自动将表B的表名称,创建时间,所有者信息存放入表A中.create trigger tri_T on B for insert as begin insert into A select * from inserted end
楼主问的是创建B表时怎么将创建表的信息插入到A表中. 上面的回答好像都是怎么查询B表信息的~
create trigger ppp on sysobjects for insert as print 'sdfsd'
--------------视图
---view create view view_ppp as select * from sysobjects
--trigger create trigger trigger_ppp on view_ppp for insert as begin if exists select * from insertd insert tt select name,crdate from insertd else
delete tt where id in (select id from deleted) end
create view view_ppp as select * from sysobjects
--trigger create trigger trigger_ppp on view_ppp instead of insert as begin if exists select * from insertd insert tt select name,crdate from insertd else
delete tt where id in (select id from deleted) end
http://blog.csdn.net/roy_88/archive/2007/12/03/1914264.aspx
不用自己建表..
sysobjects保存了你所有表的信息
for insert
as
begin
insert into A select * from inserted
end
上面的回答好像都是怎么查询B表信息的~
on
sysobjects
for
insert
as
print 'sdfsd'
--------------视图
---view
create view view_ppp
as
select * from sysobjects
--trigger
create trigger trigger_ppp
on view_ppp
for insert
as
begin
if
exists
select * from insertd
insert tt
select name,crdate from insertd
else
delete tt where id in (select id from deleted)
end
as
select * from sysobjects
--trigger
create trigger trigger_ppp
on view_ppp
instead of insert
as
begin
if
exists
select * from insertd
insert tt
select name,crdate from insertd
else
delete tt where id in (select id from deleted)
end
只要查询出来就可以了。
如果非要加到自定义的表里。
2005下,你可以用ddl触发器,很轻松实现。
要自己加的话,就麻烦, 也没什么难度,纯体力活。
create table tb(id int)做这个的时候,在后面再加一行,自己把这些信息再写一扁啦....当然,具体你要存哪些项,你自己决定(假设myTableLog是你用来记录的表,当然,为了记录其它信息,你可能还要加表,或加列。然后,每建个表,或改个表结构,都显式插一条记录到你log表.. 没啥意义insert myTableLog(name,createDate) select 'tb',getdate()
改或更新描述,参见
http://topic.csdn.net/u/20081022/12/6f8242ca-f26b-431e-8ada-5f27eeacf2c5.html
直接从sys.sysobjects字段crdate提取到创建时间(xtype='U')
从sys.sysusers可以得到所有者(sys.sysusers中字段uid 与sys.sysobjects中字段uid 关联)。--2005
从sys.sysobjects (type='U')或者 sys.tables中的字段create_date得表的创建日期
从sys.schemas得到架构名。