How can I resolve a database trigger's name with built-in functions?

Question

I have a database trigger that I use to prevent me from creating certain procedures in user databases.

It appears in sys.triggers, with an object_id, but I can't use the object_id function to find it.

SELECT OBJECT_ID(t.name, t.type) AS object_id, *
FROM   sys.triggers AS t;

Likewise, I can find it in sys.dm_exec_trigger_stats. I can't get object_name to resolve, but object_definition does.

SELECT OBJECT_NAME(dets.object_id, dets.database_id) AS object_name,
       OBJECT_DEFINITION(dets.object_id) AS object_definition,
       *
FROM   sys.dm_exec_trigger_stats AS dets;

Is there a function that will accept a database level trigger's object id, and return its name?

Aaron Bertrand · Accepted Answer · 2020-05-07T21:18:17.753

Database- and server-level triggers are not scoped as "objects" per se (this is why you cannot create them under a schema, and why they don't show up in sys.objects).

You can see that these objects have certain restrictions on them, for example in the OBJECTPROPERTY() docs:

This function cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

And similarly in the OBJECTPROPERTYEX() docs:

OBJECTPROPERTYEX cannot be used for objects that are not schema-scoped, such as data definition language (DDL) triggers and event notifications.

The OBJECT_ID() docs are a little more explicit:

Objects that are not schema-scoped, such as DDL triggers, cannot be queried by using OBJECT_ID. For objects that are not found in the sys.objects catalog view, obtain the object identification numbers by querying the appropriate catalog view. For example, to return the object identification number of a DDL trigger, use SELECT OBJECT_ID FROM sys.triggers WHERE name = 'DatabaseTriggerLog'.

The OBJECT_NAME() docs are less explicit, but they mention the same restriction implicitly (emphasis mine):

Returns the database object name for schema-scoped objects.

For the first query, not sure why you need to get the name via the function, since the name column in sys.triggers already gives you that answer. For the second query, you could just join to sys.triggers:

SELECT tr.*, ts.*
FROM sys.dm_exec_trigger_stats AS ts
LEFT OUTER JOIN sys.triggers AS tr
ON ts.[object_id] = tr.[object_id];

You could create your own function, of course, but I don't know of any built-in functions that do this correlation for you (and I recommend staying away from built-in metadata functions generally anyway).

DDL triggers are kind of a special animal. So if you're worried about also having to join to sys.procedures, sys.views, etc., don't.

score 6 · Answer 2 · answered Dec 13 '23 at 18:37

I use these two snippets to get database and server-level trigger information from DMVs.

I also include disable and enable commands because I often have to look them up.

Database-level triggers:

/* use in a context of correct DB*/
select
    t.object_id
    , t.name
    , t.create_date
    , t.modify_date
    , t.is_ms_shipped
    , t.is_disabled
    , concat('DISABLE TRIGGER ', quotename(t.name) , ' ON DATABASE') as disableCommand
    , concat('ENABLE TRIGGER ', quotename(t.name)  , ' ON DATABASE') as enableCommand
    , sm.definition as triggerDefinition
    , sm.*
from sys.triggers as t
join sys.sql_modules as sm
    on t.object_id = sm.object_id
where
    t.parent_class = 0 /* database */

Server-level triggers:

select
    st.object_id
    , st.name
    , st.create_date
    , st.modify_date
    , st.is_ms_shipped
    , st.is_disabled
    , concat('DISABLE TRIGGER ', quotename(st.name) , ' ON ALL SERVER') as disableCommand
    , concat('ENABLE TRIGGER ', quotename(st.name)  , ' ON ALL SERVER') as enableCommand
    , ssm.definition
    , ssm.uses_ansi_nulls
    , ssm.uses_quoted_identifier
    , ssm.execute_as_principal_id
from sys.server_triggers as st
join sys.server_sql_modules as ssm
    on st.object_id = ssm.object_id

How can I resolve a database trigger's name with built-in functions?

2 Answers2