For each table, I need a trigger which will update "ChangedDateTime" with current datetime if insert or update happened.:
------------------------------------------
USE [calvin zhao center]
GO
DECLARE @sql nvarchar(1000)
DECLARE @tblName nvarchar(50)
DECLARE @IdentityCol nvarchar(50)
DECLARE CursorUserTables CURSOR FOR
SELECT name FROM sys.tables
WHERE is_ms_shipped = 0
OPEN CursorUserTables
FETCH NEXT FROM CursorUserTables INTO @tblName
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @IdentityCol = COLUMN_NAME FROM INFORMATION_SCHEMA.columns
WHERE TABLE_NAME=@tblName AND COLUMNPROPERTY(OBJECT_ID(@tblName),COLUMN_NAME,‘IsIdentity‘)=1
if @IdentityCol is NULL
RAISERROR (‘This table has no identity column.‘, 16, 1)
else
begin
set @sql =‘IF OBJECT_ID (‘‘dbo.u‘ + @tblName + ‘‘‘,‘‘TR‘‘) IS NOT NULL
DROP TRIGGER dbo.u‘+ @tblName + ‘;‘
execute (@sql)
set @sql = ‘CREATE TRIGGER dbo.u‘ +@tblName + ‘
ON dbo.‘ + @tblName + ‘
AFTER UPDATE
AS
BEGIN
UPDATE ‘ + @tblName + ‘ SET ChangedDateTime = getdate()
WHERE ‘ + @identityCol + ‘=(select ‘+@IdentityCol + ‘ from inserted)
END;‘
print @sql
execute (@sql)
end
FETCH NEXT FROM CursorUserTables INTO @tblName
END
CLOSE CursorUserTables
DEALLOCATE CursorUserTables
----------------------------------------
注释:
1. 动态sql语句的回车符可以直接通过换行来得到
2.不能用GO
3. UPDATE SCHEDULES SET ChangedDateTime = getdate()
WHERE ScheduleKe=inserted.ScheduleKe 通不过,提示错误:
The multi-part identifier "inserted.ScheduleKe" could not be bound.
用 WHERE ScheduleKe=(select ScheduleKe from inserted)
