CODEWITHSUNDEEP
sqlsql-serverazuretriggersazure-sql-database
pGrnd2
pGrnd2

Reputation: 554

DDL trigger for everything

I got the famous DDL triger to trigger everything happens in a DB

https://www.mssqltips.com/sqlservertip/2085/sql-server-ddl-triggers-to-track-all-database-changes/

in the example, it is set 

FOR CREATE_PROCEDURE, ALTER_PROCEDURE, DROP_PROCEDURE

but i pretend to extend for every DDL change, so I search : https://learn.microsoft.com/en-us/sql/relational-databases/triggers/ddl-events

over 200 events !!

can we use something like ?

CREATE TRIGGER DDLTrigger_Sample
    ON DATABASE
    FOR ABSOLUTELY_EVERYTHING
AS
BEGIN

Upvotes: 2

Views: 576

Answers (1)

Ben Thul
Ben Thul

Reputation: 32697

Short answer - yes. DDL trigger events are hierarchical. You can see the relationships in sys.trigger_event_types. For instance, let's look at the hierarchy that DROP_PROCEDURE exists in, we can use this query:

WITH e AS (
    SELECT tet.type ,
           tet.type_name ,
           tet.parent_type, 1 AS level
    FROM sys.trigger_event_types AS tet
    WHERE tet.type_name = 'DROP_PROCEDURE'

    UNION ALL

    SELECT parent.type ,
           parent.type_name ,
           parent.parent_type, child.level + 1 AS level
    FROM e AS child
    JOIN sys.trigger_event_types AS parent
        ON child.parent_type = parent.type
)

SELECT *
FROM e
ORDER BY level DESC;

From that, I can see (on my server) that DROP_PROCEDURE belongs to DDL_PROCEDURE_EVENTS, , DDL_DATABASE_LEVEL_EVENTS, and DDL_EVENTS (in order of decreasing granularity). Let's say that you chose DDL_DATABASE_LEVEL_EVENTS, you could figure out everything that would be covered with the below.

WITH e AS (
    SELECT tet.type ,
           tet.type_name ,
           tet.parent_type, 1 AS level
    FROM sys.trigger_event_types AS tet
    WHERE tet.type_name = 'DDL_DATABASE_LEVEL_EVENTS'

    UNION ALL

    SELECT child.type ,
           child.type_name ,
           child.parent_type ,
           parent.level + 1 AS level
    FROM e AS parent
    JOIN sys.trigger_event_types AS child
        ON child.parent_type = parent.type
)

SELECT *
FROM e
ORDER BY level

Upvotes: 3

Related Questions