How to Get Trigger Definition in SQL Server (OBJECT_DEFINITION, sp_helptext, sys.sql_modules)

To get trigger definition in SQL Server, pass the trigger name to OBJECT_ID and wrap it in OBJECT_DEFINITION. You get the full CREATE TRIGGER text back as one value:

SET NOCOUNT ON;
SELECT OBJECT_DEFINITION(OBJECT_ID(N'dbo.trg_Orders_Audit')) AS TriggerCode;

sqlcmd printed:

CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders
AFTER INSERT, UPDATE
AS
    INSERT INTO dbo.OrderAudit (OrderID, Action, LoggedAt)
    SELECT OrderID, 'saved', SYSDATETIME() FROM inserted;

Two cases return NULL instead: DDL triggers and encrypted triggers.

Sample Triggers for Every Example

Tested on SQL Server 2025 (17.0.1000.7) Developer, sqlcmd in VS Code. I used -y 0 for long text, since sqlcmd cuts nvarchar(max) otherwise (it also hides the header).

Three table triggers plus one DDL trigger. New to triggers? Read this SQL Server trigger overview.

CREATE TABLE dbo.Orders (
    OrderID  int           PRIMARY KEY,
    Customer nvarchar(20)  NOT NULL,
    Amount   decimal(10,2) NOT NULL
);
CREATE TABLE dbo.OrderAudit (OrderID int, Action varchar(10), LoggedAt datetime2(0));
GO
CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders
AFTER INSERT, UPDATE
AS
    INSERT INTO dbo.OrderAudit (OrderID, Action, LoggedAt)
    SELECT OrderID, 'saved', SYSDATETIME() FROM inserted;
GO
CREATE TRIGGER dbo.trg_Orders_NoDelete ON dbo.Orders
INSTEAD OF DELETE
AS
    THROW 50001, 'Cancel orders instead.', 1;
GO
CREATE TRIGGER dbo.trg_Orders_Secret ON dbo.Orders
WITH ENCRYPTION
AFTER UPDATE
AS
    UPDATE dbo.OrderAudit SET Action = 'edited' WHERE 1 = 0;
GO
DISABLE TRIGGER dbo.trg_Orders_Secret ON dbo.Orders;
GO
CREATE TRIGGER trg_Db_NoDropTable ON DATABASE
FOR DROP_TABLE
AS
    PRINT 'Dropping tables is blocked here.';
    ROLLBACK;

Get Trigger Code With sp_helptext

Same name, older procedure:

EXEC sp_helptext N'dbo.trg_Orders_Audit';

Output:

CREATE TRIGGER dbo.trg_Orders_Audit ON dbo.Orders

AFTER INSERT, UPDATE

AS

    INSERT INTO dbo.OrderAudit (OrderID, Action, LoggedAt)

    SELECT OrderID, 'saved', SYSDATETIME() FROM inserted;

You should see one row per line; the Text column is nvarchar(255), so long lines wrap. To search code across objects, see find text in stored procedures.

How Do You List All Triggers in SQL Server With Their Code?

Join sys.triggers to sys.sql_modules and sys.trigger_events:

SET NOCOUNT ON;
SELECT CAST(t.name AS varchar(20))                 AS TriggerName,
       CAST(OBJECT_NAME(t.parent_id) AS varchar(6)) AS TableName,
       CASE t.is_instead_of_trigger WHEN 1 THEN 'INSTEAD OF'
            ELSE 'AFTER' END                        AS Timing,
       CAST((SELECT STRING_AGG(e.type_desc, ',')
             FROM sys.trigger_events AS e
             WHERE e.object_id = t.object_id) AS varchar(14)) AS Events,
       t.is_disabled                                AS Disabled,
       LEN(m.definition)                            AS CodeLength
FROM sys.triggers AS t
JOIN sys.sql_modules AS m ON m.object_id = t.object_id
WHERE t.parent_class = 1          -- 1 = table/view (DML) triggers
ORDER BY t.name;

Output:

TriggerName          TableName Timing     Events         Disabled CodeLength          
-------------------- --------- ---------- -------------- -------- --------------------
trg_Orders_Audit     Orders    AFTER      INSERT,UPDATE         0                  196
trg_Orders_NoDelete  Orders    INSTEAD OF DELETE                0                  124
trg_Orders_Secret    Orders    AFTER      UPDATE                1                 NULL

Check your result: the INSTEAD OF trigger is labeled, and trg_Orders_Secret shows Disabled = 1 after DISABLE TRIGGER. Its NULL CodeLength means encryption.

Get a Database DDL Trigger Definition (OBJECT_ID Returns NULL)

DDL triggers aren’t schema-scoped, so OBJECT_ID can’t find them. Filter sys.triggers on parent_class_desc = 'DATABASE' instead:

SET NOCOUNT ON;
SELECT OBJECT_ID(N'trg_Db_NoDropTable') AS ObjectIdLookup;

SELECT CAST(t.name AS varchar(18))      AS DbTrigger,
       CAST(e.type_desc AS varchar(10)) AS FiresOn,
       m.definition                     AS TriggerCode
FROM sys.triggers AS t
JOIN sys.trigger_events AS e ON e.object_id = t.object_id
JOIN sys.sql_modules AS m ON m.object_id = t.object_id
WHERE t.parent_class_desc = 'DATABASE';

Output:

NULL
trg_Db_NoDropTable DROP_TABLE CREATE TRIGGER trg_Db_NoDropTable ON DATABASE
FOR DROP_TABLE
AS
    PRINT 'Dropping tables is blocked here.';
    ROLLBACK;

The first NULL is OBJECT_ID failing.

Get a Server Trigger Definition From sys.server_sql_modules

Server triggers use sys.server_triggers and sys.server_sql_modules. My script creates one, reads it and drops it:

SET NOCOUNT ON;
GO
CREATE TRIGGER trigdef_demo_srv_audit ON ALL SERVER
FOR CREATE_DATABASE
AS PRINT 'A database was created.';
GO
SELECT CAST(st.name AS varchar(22)) AS ServerTrigger,
       sm.definition                AS TriggerCode
FROM sys.server_triggers AS st
JOIN sys.server_sql_modules AS sm ON sm.object_id = st.object_id
WHERE st.name = N'trigdef_demo_srv_audit';
GO
DROP TRIGGER trigdef_demo_srv_audit ON ALL SERVER;

Output:

trigdef_demo_srv_audit CREATE TRIGGER trigdef_demo_srv_audit ON ALL SERVER
FOR CREATE_DATABASE
AS PRINT 'A database was created.';

To guard a CREATE, see create a trigger if it doesn’t exist.

Why Is the Trigger Definition NULL? WITH ENCRYPTION

Now the encrypted one; even sp_helptext refuses:

SET NOCOUNT ON;
SELECT LEN(OBJECT_DEFINITION(OBJECT_ID(N'dbo.trg_Orders_Secret')))
           AS CodeLength,
       CAST(OBJECTPROPERTY(OBJECT_ID(N'dbo.trg_Orders_Secret'),
            'IsEncrypted') AS char(1)) AS IsEncrypted;
EXEC sp_helptext N'dbo.trg_Orders_Secret';

Output:

CodeLength           IsEncrypted
-------------------- -----------
                NULL 1          
The text for object 'dbo.trg_Orders_Secret' is encrypted.

Microsoft defines a NULL definition as encrypted. Keep the script in source control; SQL Server won’t give it back.

Permissions: VIEW DEFINITION on the Trigger’s Table

Unencrypted but you get NULL? It’s permissions. I tested a SELECT-only user, then granted VIEW DEFINITION on the table:

SET NOCOUNT ON;
DROP USER IF EXISTS ReportReader;
CREATE USER ReportReader WITHOUT LOGIN;
GRANT SELECT ON dbo.Orders TO ReportReader;

EXECUTE AS USER = N'ReportReader';
SELECT LEN(OBJECT_DEFINITION(OBJECT_ID(N'dbo.trg_Orders_Audit')))
       AS SelectOnly;
REVERT;

GRANT VIEW DEFINITION ON dbo.Orders TO ReportReader;
EXECUTE AS USER = N'ReportReader';
SELECT LEN(OBJECT_DEFINITION(OBJECT_ID(N'dbo.trg_Orders_Audit')))
       AS WithViewDefinition;
REVERT;

Output:

SelectOnly          
--------------------
                NULL
WithViewDefinition  
--------------------
                 196

NULL first, then 196 characters. ALTER, CONTROL or TAKE OWNERSHIP work too.

Common Mistake: sp_helptext Msg 15009

SET NOCOUNT ON;
BEGIN TRY EXEC sp_helptext N'trg_Db_NoDropTable'; END TRY
BEGIN CATCH PRINT CONCAT('Msg ', ERROR_NUMBER(), ': ', ERROR_MESSAGE()); END CATCH;

Output:

Msg 15009: The object 'trg_Db_NoDropTable' does not exist in database 'trigdef_demo' or is invalid for this operation.

sp_helptext only takes schema-scoped objects, so your DDL trigger fails. Use the DDL query above, or check first like in trigger IF EXISTS with RAISERROR.

Where to Go Next After You Read the Trigger Code

Next step: read the inserted table inside a trigger. On MariaDB, see MariaDB CREATE TRIGGER.

More trigger tasks:

• Write an AFTER INSERT trigger
• If the trigger sits on a view, read triggers on views
• Drop a trigger only if it exists

Frequently Asked Questions