监视使用触发器重命名sql server数据库中的表 [英] monitor renaming a table in a sql server database with trigger
问题描述
我有一个触发器,用于记录对数据库中表的元数据所做的更改。
会将日志保存在另一个名为 tblMonitorChange 的表中。
重命名表时不会生成任何触发器。
I have a trigger that logs on changes to the metadata of tables in a database. it saves the logs in another table called tblMonitorChange. When i rename a table no triggers is generated.
这是触发器:
USE ReportServer
GO
CREATE TRIGGER trgMonitorChange
ON DATABASE
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE , RENAME_TABLE
AS
set nocount on
declare @EventType varchar(100)
declare @SchemaName varchar(100)
declare @ObjectName varchar(100)
declare @ObjectType varchar(100)
SELECT
@EventType = EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(max)')
,@SchemaName = EVENTDATA().value('(/EVENT_INSTANCE/SchemaName)[1]','nvarchar(max)')
,@ObjectName = EVENTDATA().value('(/EVENT_INSTANCE/ObjectName)[1]','nvarchar(max)')
,@ObjectType = EVENTDATA().value('(/EVENT_INSTANCE/ObjectType)[1]','nvarchar(max)')
-- Is the default schema used
if @SchemaName = ' ' select @SchemaName = default_schema_name from sys.sysusers u join sys.database_principals p
on u.uid = p.principal_id where u.name = CURRENT_USER
insert into tblMonitorChange
select @EventType, @SchemaName, @ObjectName, @ObjectType, getdate(), SUSER_SNAME()
这是以下示例输出:1)创建TestTable,2)将其重命名为TestTable2,然后3)在其中添加一列。
this is a sample output on 1) creating TestTable, 2)renaming it to TestTable2 and then 3) adding a column to it.
EventType SchemaName ObjectName ObjectType EventDate
CREATE_TABLE dbo TestTable TABLE 2017-11-01 10:55:44.590
ALTER_TABLE dbo TestTable2 TABLE 2017-11-01 14:36:07.543
但是重命名尚未记录。
But renaming has not been logged.
那么我如何监视表的重命名?
So how can i monitor renaming the table?
任何帮助将不胜感激。
Any help would be appreciated.
推荐答案
可以用RENAME事件替换Rename_table事件吗?下面的代码可以正常工作
Can you replace Rename_table event with RENAME event? Below Codes working properly
CREATE TRIGGER ddl_trigger_alter ON DATABASE
WITH EXECUTE AS 'dbo'
FOR CREATE_TABLE, ALTER_TABLE, DROP_TABLE,RENAME
AS
DECLARE @ddltriggerxml XML;
SELECT @ddltriggerxml = EVENTDATA();
SELECT @ddltriggerxml;
GO
CREATE TABLE TEST(ID INT)
go
sp_rename 'test_new','test'
这篇关于监视使用触发器重命名sql server数据库中的表的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!