Как получить скрипт хранимой процедуры при срабатывании триггера ddl?

Я пишу триггеры ddl для создания и изменения хранимых процедур.

Для создания триггера хранимой процедуры я хочу получить сценарий для оператора создания. Для триггера изменения хранимой процедуры я хочу получить новый оператор «создать хранимую процедуру» вместо оператора изменения.

Есть ли способ сделать это? Для первого я знаю, что eventdata() может. А вот на счет второго я не уверен.


stored-procedures sql-server-2008 ddl
person Joshua H.    schedule 05.06.2012    source источник
comment
Какие технологии вы можете использовать? TSQL? CLR? Другой .NET?   -  person JNK    schedule 05.06.2012
comment
Значит, вам не нравится, что триггер DDL захватывает команду ALTER? Разве вы не можете преобразовать его в команду CREATE с помощью STUFF(cmd, 1, 5, 'CREATE')?   -  person Aaron Bertrand    schedule 05.06.2012

Ответы (1)


arrow_upward
3
arrow_downward

В триггере DDL вы можете получить изображение «после» процедуры, используя:

DECLARE @EventData XML = EVENTDATA();

SELECT OBJECT_DEFINITION(OBJECT_ID(
   @EventData.value('(/EVENT_INSTANCE/SchemaName)[1]', 'NVARCHAR(255)')
   + '.' + 
   @EventData.value('(/EVENT_INSTANCE/ObjectName)[1]', 'NVARCHAR(255)')
));

Таким образом, вы можете назначить этот вывод переменной и сделать его частью своей вставки (я предполагаю, что вы записываете это в какую-то таблицу аудита DDL). Как я предложил в комментарии, вы также можете сделать:

DECLARE @EventData XML = EVENTDATA();

SELECT STUFF(@EventData.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'NVARCHAR(MAX)'), 
  1, 5, 'CREATE');

(Оба они предполагают, что ваш триггер DDL ограничен ALTER_PROCEDURE — в противном случае вы захотите проверить тип события перед выполнением этих операций.)

person Aaron Bertrand    schedule 05.06.2012
comment
Всего несколько предостережений: (1) это предполагает, что вы захватываете изображение после того, как ваш код сможет вызвать откат (на случай, если вы пытаетесь применить какие-либо политики через триггер DDL). (2) подход STUFF зависит от того, что первые 5 символов равны ALTER. Если вы делаете нестандартные вещи, например, размещаете комментарии перед оператором CREATE/ALTER или ставите возврат каретки или другие пробелы перед CREATE/ALTER, вам придется соответствующим образом настроить. Первый способ в этом отношении более безопасен. - person Aaron Bertrand; 05.06.2012