Un des avantages des triggers DDL est de permettre la prévention contre tout changement non désiré. Après avoir eu une discussion avec Elsuket, nous nous sommes demandé comment empêcher la désactivation d’un trigger DML par la commande DISABLE TRIGGER.

En effet, les événements CREATE TRIGGER, DROP TRIGGER sont gérés par les triggers DDL alors que les événements DISABLE TRIGGER et ENABLE TRIGGER ne le sont pas. Par conséquent ces derniers ne sont pas interceptés par le gestionnaire d’événements EVENTDATA() des triggers DDL. Pour s’en convaincre il suffit d’appliquer ce petit exemple :

Créons une table et un trigger associé à l’événement INSERT

CREATE table test_table
(
id INT,
test VARCHAR(10)
);
GO

CREATE TRIGGER test_tr
ON dbo.test_table
FOR INSERT
AS

PRINT ‘INSERT OK';
GO

Créons maintenant un trigger DDL qui interceptera tous les événements d’étendue base de données :

CREATE TRIGGER test_intercept_all_events_database
ON DATABASE
FOR DDL_DATABASE_LEVEL_EVENTS
AS
BEGIN
    DECLARE @data XML = EVENTDATA();
    SELECT @data.value(‘(/EVENT_INSTANCE/EventType)[1]’, ‘nvarchar(100)’)
END;

Vérifions maintenant l’interception d’événements (via la commande ALTER TABLE par exemple) :

ALTER TABLE dbo.test_table
ADD verif BIT NULL DEFAULT 0;

L’événement est bien intercepté avec le résultat suivant :

event
————-
ALTER_TABLE

Maintenant voyons ce qui se passe lorsqu’on désactive notre trigger DML :

DISABLE TRIGGER dbo.test_tr ON dbo.test_table;

Vous remarquez qu’aucun résultat n’est retourné ce qui veut dire que la commande DISABLE TRIGGER n’est pas intercepté par le gestionnaire d’événements EVENTDATA(). Quelle est la solution dans ce cas ? Pour le moment aucune solution miracle mais une solution de contournement existe, bien que celle-ci ne soit pas parfaite car la désactivation d’un trigger doit passer non pas par la commande DISABLE TRIGGER mais par la commande ALTER TABLE table_name DISABLE TRIGGER trigger_name. Dès lors il est facile dans ce cas de créer un trigger DDL qui interceptera l’événement ALTER_TABLE et interdira toute désactivation de trigger.

CREATE TRIGGER test_prevent_disable_trigger_dml
ON DATABASE
FOR alter_table
AS
BEGIN
    DECLARE @data XML = EVENTDATA();
    IF EXISTS(SELECT * FROM @data.nodes(‘/EVENT_INSTANCE/AlterTableActionList/Disable/Triggers[1]’)AS n(t))
    BEGIN
        PRINT ‘Désactivation de triggers DML interdite';
        ROLLBACK;
    END;
END;

Testons maintenant la désactivation du trigger test_tr par la commande ALTER TABLE :

ALTER TABLE dbo.test_table DISABLE TRIGGER test_tr;

Nous obtenons bien le résultat escompté :

Désactivation de triggers DML interdite
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted

Si d’autres idées vous viennent je suis preneur…

David BARBARIN (Mikedavem)

Elève ingénieur CNAM Lyon