USE Test;
GO
CREATE TABLE alter_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100),dbName nvarchar(32), TSQL nvarchar(2000),EventData xml);
GO
CREATE TRIGGER ddl_alter_log
ON all server
after CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS
INSERT into Test..alter_log
(PostTime, DB_User, Event, dbName,TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
DB_NAME(),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
GO
ENABLE TRIGGER ddl_alter_log ON ALL SERVER这个有什么问题吗,该怎么处理呢?
GO
CREATE TABLE alter_log (PostTime datetime, DB_User nvarchar(100), Event nvarchar(100),dbName nvarchar(32), TSQL nvarchar(2000),EventData xml);
GO
CREATE TRIGGER ddl_alter_log
ON all server
after CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS
INSERT into Test..alter_log
(PostTime, DB_User, Event, dbName,TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
DB_NAME(),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
GO
ENABLE TRIGGER ddl_alter_log ON ALL SERVER这个有什么问题吗,该怎么处理呢?
一个星星呢!呵呵。
等你解决出来我copy 一下!!!
ON database
after CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
AS
INSERT into Test..alter_log
(PostTime, DB_User, Event, dbName,TSQL)
VALUES
(GETDATE(),
CONVERT(nvarchar(100), CURRENT_USER),
EVENTDATA().value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(100)'),
DB_NAME(),
EVENTDATA().value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(2000)') )
GO
after CREATE_TABLE,DROP_TABLE,ALTER_TABLE,CREATE_PROCEDURE,ALTER_PROCEDURE,DROP_PROCEDURE
-----------------------------------------------------------------------------------这些事件是: 数据库作用域的 DDL 语句
CREATE_APPLICATION_ROLE(应用于 CREATE APPLICATION ROLE 语句和 sp_addapprole。如果创建新架构,则此事件也触发 CREATE_SCHEMA 事件。)
ALTER_APPLICATION_ROLE(应用到 ALTER APPLICATION ROLE 语句和 sp_approlepassword。)
DROP_APPLICATION_ROLE(应用到 DROP APPLICATION ROLE 语句和 sp_dropapprole。)
CREATE_ASSEMBLY
ALTER_ASSEMBLY
DROP_ASSEMBLY
ALTER_AUTHORIZATION_DATABASE(应用到 sp_changedbowner,在指定 ON DATABASE 时,还可应用到 ALTER AUTHORIZATION 语句。)
CREATE_CERTIFICATE
ALTER_CERTIFICATE
DROP_CERTIFICATE
CREATE_CONTRACT
DROP_CONTRACT
GRANT_DATABASE
DENY_DATABASE
REVOKE_DATABASE
CREATE_EVENT_NOTIFICATION
DROP_EVENT_NOTIFICATION
CREATE_FUNCTION
ALTER_FUNCTION
DROP_FUNCTION
CREATE_INDEX
ALTER_INDEX
DROP_INDEX
CREATE_MESSAGE_TYPE
ALTER_MESSAGE_TYPE
DROP_MESSAGE_TYPE
CREATE_PARTITION_FUNCTION
ALTER_PARTITION_FUNCTION
DROP_PARTITION_FUNCTION
CREATE_PARTITION_SCHEME
ALTER_PARTITION_SCHEME
DROP_PARTITION_SCHEME
CREATE_PROCEDURE
ALTER_PROCEDURE
DROP_PROCEDURE
CREATE_QUEUE
ALTER_QUEUE
DROP_QUEUE
CREATE_REMOTE_SERVICE_BINDING
ALTER_REMOTE_SERVICE_BINDING
DROP_REMOTE_SERVICE_BINDING
CREATE_ROLE(应用到 CREATE ROLE 语句、sp_addrole 和 sp_addgroup。)
ALTER_ROLE
DROP_ROLE(应用到 DROP ROLE 语句、sp_droprole 和 sp_dropgroup。)
CREATE_ROUTE
ALTER_ROUTE
DROP_ROUTE
CREATE_SCHEMA(应用到 CREATE SCHEMA 语句、sp_addrole、sp_adduser、sp_addgroup 和 sp_grantdbaccess。)
ALTER_SCHEMA(应用到 ALTER SCHEMA 语句和 sp_changeobjectowner。)
DROP_SCHEMA
CREATE_SERVICE
ALTER_SERVICE
DROP_SERVICE
CREATE_STATISTICS
DROP_STATISTICS
UPDATE_STATISTICS
CREATE_SYNONYM
DROP_SYNONYM
CREATE_TABLE
ALTER_TABLE
DROP_TABLE
CREATE_TRIGGER
ALTER_TRIGGER
DROP_TRIGGER
CREATE_TYPE(应用到 CREATE TYPE 语句和 sp_addtype。)
DROP_TYPE(应用到 DROP TYPE 语句和 sp_droptype。)
CREATE_USER(应用到 CREATE USER 语句、sp_adduser 和 sp_grantdbaccess。)
ALTER_USER
DROP_USER(应用到 DROP USER 语句、sp_dropuser 和 sp_revokedbaccess。)
CREATE_VIEW
ALTER_VIEW
DROP_VIEW
CREATE_XML_SCHEMA_COLLECTION
ALTER_XML_SCHEMA_COLLECTION
DROP_XML_SCHEMA_COLLECTION
服务器作用域的 DDL 语句
ALTER_AUTHORIZATION_SERVER
CREATE_DATABASE
ALTER_DATABASE
DROP_DATABASE
CREATE_ENDPOINT
ALTER_ENDPOINT
DROP_ENDPOINT
CREATE_LOGIN(用于必须隐式创建的不存在的登录时,应用到 CREATE LOGIN 语句、sp_addlogin、sp_grantlogin、xp_grantlogin 和 sp_denylogin。)
ALTER_LOGIN(指定 Auto_Fix 时,应用到 ALTER LOGIN 语句、sp_defaultdb、sp_defaultlanguage、sp_password 和 sp_change_users_login。)
DROP_LOGIN(应用到 DROP LOGIN 语句、sp_droplogin、sp_revokelogin 和 xp_revokelogin。)
GRANT_SERVER
DENY_SERVER
REVOKE_SERVER