添加“DateModified"对包含“DateCreated"的所有表具有更新触发器的列柱子 [英] Add "DateModified" Column with an Update Trigger To All Tables that contain a "DateCreated" Column
问题描述
我有很多表有一个 [DateCreated]
列,也需要一个 [DateModified]
列.
I have many tables that have a [DateCreated]
column that also needs a [DateModified]
column.
[DateModified]
列需要一个 Update
触发器来将当前日期 (getdate()
) 插入到这个新的 中[DateModified]
列.
The [DateModified]
column will need an Update
trigger that inserts the current date (getdate()
) into this new [DateModified]
column.
每个表都使用自定义架构,而不是 [dbo]
.
Each table uses custom schema, not [dbo]
.
最简单/最好的方法是什么?
Whats the easiest/best way to do this?
推荐答案
这将为您提供alter table 语句,您可以将其剪切/粘贴到新的查询窗口中以执行.我将把它留作练习,让读者使用相同的技术来生成创建触发器语句.
This will get you the alter table statements, which you can cut/paste into a new query window to execute. I'll leave it as an exercise for the reader to use this same technique to generate the create trigger statements.
select 'alter table ' + quotename(s.name) + '.' + quotename(t.name) + ' add [DateModified] datetime'
from sys.columns c
inner join sys.tables t
on c.object_id = t.object_id
inner join sys.schemas s
on t.schema_id = s.schema_id
left join sys.columns c2
on t.object_id = c2.object_id
and c2.name = 'DateModified'
where c.name = 'DateCreated'
and t.type = 'U'
and c2.column_id is null /* DateModified column does not already exist */
这篇关于添加“DateModified"对包含“DateCreated"的所有表具有更新触发器的列柱子的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!