问题描述
我将为 SQL Server 实例中的所有数据库创建一个 DDL 触发器.我想一次运行而不是为每个数据库运行多次.
I am going to create a DDL trigger to all databases in SQL Server instance. I'd like to do it in one run instead of many runs for each database.
下面是我需要执行的两条 T-SQL 语句:
Below are the two T-SQL statements I need to execute:
-- Create table
use <dbname>
GO
CREATE TABLE dbo.ChangeAttempt
(EventData xml NOT NULL,
AttemptDate datetime NOT NULL DEFAULT GETDATE(),
DBUser char(50) NOT NULL)
GO
-- Create DDL trigger
use <dbname>
GO
CREATE TRIGGER db_trg_ObjectChanges
ON DATABASE
FOR ALTER_PROCEDURE, DROP_PROCEDURE,
ALTER_INDEX, DROP_INDEX,
ALTER_TABLE, DROP_TABLE, ALTER_TRIGGER, DROP_TRIGGER,
ALTER_VIEW, DROP_VIEW, ALTER_SCHEMA, DROP_SCHEMA,
ALTER_ROLE, DROP_ROLE, ALTER_USER, DROP_USER
AS
SET NOCOUNT ON
INSERT dbo.ChangeAttempt
(EventData, DBUser)
VALUES (EVENTDATA(), USER)
GO
我的问题是:如何在一次运行中以编程方式创建 DDL 触发器?
My question is: how can I programmaticaly create DDL trigger in one run?
推荐答案
为什么需要一次运行?这是唯一的方法.
why do you need one run? this is the only way to do it.
Msg 111, Level 15, State 1, Line 2
'CREATE TRIGGER' must be the first statement in a query batch.
运行由此产生的输出:
DECLARE @DatabaseName varchar(500)
DECLARE @Database_id int
DECLARE @Query varchar(8000)
DECLARE @CRLF char(2)
SET @CRLF=CHAR(13)+CHAR(10)
---MODIFY THIS TO INCLUDE THE DATABASES THAT YOU WANT TO WORk ON
---MODIFY THIS TO INCLUDE THE DATABASES THAT YOU WANT TO WORk ON
select @Database_id=MIN(database_id) from sys.databases where database_id IN (5,7,8,6)
WHILE @Database_id IS NOT NULL
BEGIN
SELECT @DatabaseName=name from sys.databases where database_id=@Database_id
SET @Query='-- Create table'+@CRLF+@CRLF
+'use '+@DatabaseName+@CRLF
+' GO'+@CRLF
+' CREATE TABLE dbo.ChangeAttempt'+@CRLF
+' (EventData xml NOT NULL,'+@CRLF
+' AttemptDate datetime NOT NULL DEFAULT GETDATE(),'+@CRLF
+' DBUser char(50) NOT NULL)'+@CRLF
+'GO'+@CRLF+@CRLF
+'-- Create DDL trigger '+@CRLF+@CRLF
+'use '+@DatabaseName+@CRLF
+'GO'+@CRLF
+'CREATE TRIGGER db_trg_ObjectChanges'+@CRLF
+'ON DATABASE'+@CRLF
+'FOR ALTER_PROCEDURE, DROP_PROCEDURE,'+@CRLF
+' ALTER_INDEX, DROP_INDEX,'+@CRLF
+' ALTER_TABLE, DROP_TABLE, ALTER_TRIGGER, DROP_TRIGGER,'+@CRLF
+' ALTER_VIEW, DROP_VIEW, ALTER_SCHEMA, DROP_SCHEMA,'+@CRLF
+' ALTER_ROLE, DROP_ROLE, ALTER_USER, DROP_USER'+@CRLF
+'AS'+@CRLF
+'SET NOCOUNT ON'+@CRLF
+'INSERT dbo.ChangeAttempt'+@CRLF
+'(EventData, DBUser)'+@CRLF
+'VALUES (EVENTDATA(), USER)'+@CRLF
+'GO'+@CRLF
PRINT @Query
---MODIFY THIS TO INCLUDE THE DATABASES THAT YOU WANT TO WORk ON
---MODIFY THIS TO INCLUDE THE DATABASES THAT YOU WANT TO WORk ON
select @Database_id=MIN(database_id) from sys.databases WHERE database_id IN (5,7,8,6) AND database_id>@Database_id
END
编辑
确定要为哪些数据库生成脚本,请执行以下操作:
EDIT
to determine what databases to generate scripts for do the following:
运行这个查询:
run this query:
select database_id,name from sys.databases
select database_id,name from sys.databases
找到您要为其运行脚本的所有数据库
find all of the databases you want to run the scripts for
在两个地方更改我上面的脚本(在循环之前和在循环底部),以便您想要的所有 database_id 都在以下代码部分中:
change my above script in two places (before loop & at bottom in loop) so all of the database_id that you want are in the following code section:
WHERE database_id IN (AAA,BBB,CCC,DDD,....)
WHERE database_id IN (AAA,BBB,CCC,DDD,....)
这篇关于如何为 SQL Server 2005 实例中的所有数据库创建 DDL 触发器的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!