开发者

List all triggers in a database

Is it possible to list all triggers in all tables on a database? I can list all user stored procedures with:

Private Sub StoredPro()
    Dim theServer As Server = New Server(serverName)
    Dim myDB As Database = theServer.Databases("mydatabase")
    Dim dt As DataTable = myDB.EnumObjects(DatabaseObjectTypes.StoredProcedure)
End Sub

But nothing obvious in SMO for triggers. (I'm actually trying to list just custom triggers, any that are not named DEL_t开发者_如何学编程ablename, INS_tablename, UPD_tablename) within the selected database.


https://social.msdn.microsoft.com/forums/sqlserver/en-US/bb024fd6-c0b9-441a-864e-a579fc441267/list-databasetable-triggers-via-smo

A DatabaseDdlTrigger object represents a data definition language (DDL) trigger, which is created on a SQL Server database. However, from your description, I think you want to iterate over the data manipulation language (DML) triggers, which are created on SQL Server tables.

Please refer to the following sample:

Dim srv As New Server
Dim db As Database = srv.Databases.Item("Northwind")
Dim tb As Table
For Each tb In db.Tables
  Dim trg As Trigger
  For Each trg In tb.Triggers
    If (((trg.Name <> ("INS_" & tb.Name)) AndAlso (trg.Name <> ("DEL_" & tb.Name))) AndAlso (trg.Name <> ("UPD_" & tb.Name))) Then
      MessageBox.Show(trg.Name)
    End If
  Next
Next


Each database object has a Triggers property, which you can iterate to find all triggers.
Once you have a reference to this trigger, check the IsSystemObject property to check if it is a user-defined trigger or not.

foreach ( DatabaseDdlTrigger oTrigger in oDatabase.Triggers) {
if (! oTrigger.IsSystemObject) {
    // do something
}}

Have fun.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜