Dobrą praktyką stosowaną w wielu firmach jest narzucenie programistom i administratorom pracującym z bazami danych konwencji nazewniczych dotyczących nazw obiektów w bazach danych. Jedną z częściej stosowanych konwencji jest wyróżnianie obiektów według ich typów przez stosowanie w nazwach odpowiednich prefiksów. I tak, widoki często nazywamy z prefiksem “v”, a dla funkcji niektórzy stosują prefiks “ufn”. Jaka by nie była konwencja, trzeba pomyśleć, jak narzucić ją zapominalskim.
W SQL Server 2008 do tego zadania świetnie nadają się wyzwalacze DDL (DDL triggers). Przykładowy wyzwalacz narzucający pewną, dość ograniczoną, konwencję nazewniczą, może wyglądać tak:
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_NamingTemplateProcedural')
DROP TRIGGER tr_DDL_NamingTemplateProcedural ON DATABASE;
GO
CREATE TRIGGER tr_DDL_NamingTemplateProcedural
ON DATABASE
FOR
CREATE_PROCEDURE,
CREATE_VIEW,
CREATE_FUNCTION,
CREATE_TRIGGER
AS
BEGIN
DECLARE
-- szablony nazw
@ProcedureTemplate sysname = 'usp[_]%',
@FunctionTemplate sysname = 'ufn[_]%',
@TriggerTemplate sysname = 'tr[_]%',
@ViewTemplate sysname = 'v[_]%',
--
@NamingTemplate sysname,
@EventData xml,
@ObjectType sysname,
@ObjectName sysname,
@Message nvarchar(1000);
SET
@EventData = EVENTDATA();
SELECT
@ObjectName =
@EventData.value('(/EVENT_INSTANCE/ObjectName/text())[1]', 'sysname'),
@ObjectType =
@EventData.value('(/EVENT_INSTANCE/ObjectType/text())[1]', 'sysname');
SET
@NamingTemplate =
CASE @ObjectType
WHEN 'PROCEDURE' THEN @ProcedureTemplate
WHEN 'FUNCTION' THEN @FunctionTemplate
WHEN 'TRIGGER' THEN @TriggerTemplate
WHEN 'VIEW' THEN @ViewTemplate
ELSE '%'
END;
SET
@Message = 'Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie (' +
@ObjectType + ' LIKE ''' + REPLACE(@NamingTemplate, '%', '%%') + ''')';
IF @ObjectName NOT LIKE @NamingTemplate BEGIN
RAISERROR(@Message, 16, 1);
ROLLBACK;
END;
END;
GO
Wyzwalacz ten narzuca w bieżącej bazie danych następującą konwencję: nazwy procedur muszą zaczynać się od prefiksu usp_, nazwy funkcji – od prefiksu ufn_, nazwy wyzwalaczy – od prefiksu tr_, zaś nazwy widoków – od prefiksu v_. Wyzwalacz jest uruchamiany w przypadku próby stworzenia któregokolwiek z wymienionych obiektów w bazie danych, wydobywa z pomocą funkcji EVENTDATA i metody value (metoda typu danych XML) niezbędne informacje – nazwę obiektu oraz jego typ, sprawdza, czy nazwa jest zgodna z konwencją, a jeśli napotka niezgodność, wycofuje transakcję tworząca obiekt zwracając przy tym stosowny komunikat.
I tak, próba utworzenia przykładowej procedury:
CREATE PROC sp_SampleProc AS SELECT 1;
zakończy się zwróceniem następującego komunikatu błędu:
Msg 50000, Level 16, State 1, Procedure tr_DDL_NamingTemplateProcedural, Line 39 Nazwa obiektu niezgodna z narzuconą konwencją nazewniczą w bazie (PROCEDURE LIKE 'usp[_]%') Msg 3609, Level 16, State 2, Procedure sp_SampleProc, Line 1 The transaction ended in the trigger. The batch has been aborted.
Ktoś mógłby powiedzieć, że taki efekt można osiągnąć stosując mechanizm Policy-Based Management. Prawda. Ale wyzwalacz DDL ma kilka zalet w porównaniu ze wspomnianym mechanizmem:
- wyzwalacza DDL można użyć już w SQL Server 2005 (wymaga odrobiny zmiany kodu przy deklaracjach zmiennych),
- w przypadku wyzwalacza DDL mamy pełną kontrolę nad tym, co i jak jest sprawdzane oraz jakie zwracamy komunikaty (sami piszemy kod), jesteśmy też w stanie zaimplementować bardziej skomplikowaną logikę,
- w przypadku Policy-Based Management trzeba dobrze poznać szablony (facets), bo niektóre z nich nie działają w trybie On Change – Prevent (spróbujcie założyć polisę na nazwę widoku z użyciem szablonu View, to zrozumiecie, o co chodzi).
I jeszcze mała uwaga. W SQL Server istnieje niestety możliwość zmiany nazwy obiektu przy użyciu procedury sp_rename. Dokonywanie takich zmian jest bardzo złym pomysłem. Jednak na SQL Server 2008 / 2008 R2 administrator może przed takimi pomysłami zabezpieczyć się kolejnym wyzwalaczem DDL:
IF EXISTS (SELECT * FROM sys.triggers WHERE name = 'tr_DDL_DenyRename')
DROP TRIGGER tr_DDL_DenyRename ON ALL SERVER;
GO
CREATE TRIGGER tr_DDL_DenyRename
ON ALL SERVER
FOR RENAME
AS
BEGIN
RAISERROR('Zmiany nazw obiektów są zabronione.', 16, 1);
ROLLBACK;
END;
GO
