Is it possible to get all triggers associated with a table using sql server 2008 clr programming?

Question

Hi All,

Is it possible to use .net (C# SQL CLR) to find all the triggers associated with a table?

And also will I be able to determine the type of that trigger? - CLR or T-SQL?

Thanks, Chaks

Answer 1

Well we've got the SQL Server Management Objects including Database.Triggers, or you can always consume some T-SQL commands from within .NET and look at some of the system views such as sys.triggers if you have permissions.

Answer 2

You could do it with a query againts the system tables. Something like the following:

SELECT  tr.name as TriggerName,
        tr.type_desc TriggerType,
        tbl.name TableName,
        sch.table_schema SchemaName
FROM    sys.objects tr
INNER JOIN sys.objects tbl
    ON (tr.parent_object_id = tbl.object_id
        AND tbl.name = 'SomeTableName')
INNER JOIN information_schema.tables sch
    ON (tbl.name = sch.table_name)
WHERE tr.type IN ('TR', 'TA')

NOTE: type_desc values are CLR_TRIGGER and SQL_TRIGGER respectively.