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.
精彩评论