아래문처럼 데이터베이스명을 가지고 와서 커서를 돌려서 데이터베이스 마다 TRIGGER를 생성하는 create 문을 실행하려고 합니다.
그런데 아무래도 USE + @NAME 쪽이 안먹는거 같아요.
이런경우는 어떻게 하면 좋을까여? ㅠ
DECLARE @name VARCHAR(50) -- database name
declare @strSQL nvarchar(2000)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM MASTER.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
select @strSQL =
' use ' + @name
EXEC sp_executesql @strSQL
SELECT @strSQL = '
CREATE TRIGGER [DDLTrigger]
ON DATABASE
FOR DDL_PROCEDURE_EVENTS, DDL_TABLE_EVENTS
AS
BEGIN
SET NOCOUNT ON;
dECLARE
@EventData XML = EVENTDATA();
END
'
exec sp_executesql @strSQL
SELECT @strSQL = '
ENABLE TRIGGER [DDLTrigger] ON DATABASE
'
exec sp_executesql @strSQL
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
Comment 4
-
더따뜻한
2015.02.03 11:03
안그래도 검색해보면 Server level DDL Trigger가 있는데 잘 안되서 한참 헤매고 있었는데요. 감사합니다~!
저는 DDL 트리거를 사용해서 얻은 정보를 특정 데이터베이스 안에 공통으로 저장하려고 하거든요. ^^ 응용하여 사용하도록 하겠습니다.
그런데요. EXECUTE AS 'sa' 이부분은 이렇게 써도 문제는 없는것인가요? 제가 잘 몰라서요. ㅠ
-
권한이 없더라도 ddl문 실행 기록이 테이블에 저장되도록 하기 위해 사용했습니다.
보안 문제는 특별히 없을것으로 보입니다. (남들도 다 그렇게 쓰더군요. ㅋㅋㅋ) ㅠㅠ;;
-
더따뜻한
2015.02.03 11:31
예~ 감사합니다~
음... USE문 사용하기가 쉽지 않을겁니다. 왜 그런지는 잘... -_-;;
DDL문을 추적하기 위해 각 DB마다 DDL 트리거를 만들려고 하시는거죠?
저는 ON DATABASE가 아니고 ON ALL SERVER 하나만 만들어서 사용중입니다.
DDL트리거 사용하기
소스 내용을 간략히 소개하자면
EVENT_DATA()에서 어떤 DB에서 DDL 이벤트가 일어났는지 확인 할 수 있기 때문에
그 DB에 DDLHistory라는 테이블이 있는지 검사한다음 없으면 만듭니다.
그리고 DDL문이 발생할때마다 테이블에 저장합니다.
이런 경우 변경된 DDL문만 확인이 가능하기 때문에
SP들의 소스를 SYSCOMMENTS 테이블을 확인해서 한방에 싹 넣어버립니다.