Всем привет, поставлена следующая задача, требуется при CREATE или ALTER процедуры или функции, проверить, оставил ли разработчик комментарий в коде, если нет, то заполнить автоматически ключевые поля, кто/когда создали или исправил.
Я решил не заморачиваться проверкой оставил ли сам разработчик описание, а автоматически оставлять свои "метаданные".
В голову пришло написать такой DDL триггер:
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
12.
13.
14.
15.
16.
17.
18.
19.
20.
21.
22.
23.
24.
25.
26.
27.
28.
ALTER TRIGGER [ddl_test]
ON ALL SERVER
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
set nocount on;
set xact_abort off;
DECLARE @data XML = EVENTDATA()
DECLARE @DBname NVARCHAR(255) = @data.value('(/EVENT_INSTANCE/DatabaseName)[1]', 'nvarchar(250)')
DECLARE @ObjectName NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(250)')
DECLARE @EventType NVARCHAR(250) = @data.value('(/EVENT_INSTANCE/EventType)[1]', 'nvarchar(250)')
DECLARE @TSQLCommand NVARCHAR(MAX) = @data.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')
BEGIN TRY
IF (@EventType != 'UPDATE_STATISTICS' and @DBName != 'tempdb')
BEGIN
INSERT INTO [ADMIN].dbo.ddl_log
([db_name], [db_object_name], [action_name], [tsql_code],author_name)
VALUES (@DBName, @ObjectName, @EventType, @TSQLCommand,original_login())
END
END TRY
BEGIN CATCH
select 'xact_abort = off', error_message();
END CATCH
END
GO
В результате получаю нужные мне поля:
[db_name], [db_object_name], [action_name], [tsql_code],author_name
Но тк как в данном примере я только ловлю уже состоявшееся событие, то теперь мне нужно внести изменения в тело процедуры, добавив описание применив ALTER.
Добавляю в триггер изменения текст запроса на ALTER:
1.
2.
3.
4.
5.
6.
if (@EventType='CREATE_PROCEDURE')
begin
set @proc_charindex=CHARINDEX('PROC',@TSQLCommand)
set @TSQLCommand ='/*DISCRIPTIONS*/'+Char(13)+' ALTER '+ SUBSTRING(@TSQLCommand,@proc_charindex,len(@TSQLCommand))
--execute sp_sqlexec @TSQLCommand
end
Осталось последнее - это выполнить
execute sp_sqlexec @TSQLCommand в контексте нужной базы данных, как я уже понял, использовать USE [db_name] или [db_name].[dbo].[proc_name] у меня не получится.
Что тут можно использовать?
