SQLDMO - Adding a New Trigger

Let's say you want to add a new trigger to an existing table. Although it is possible to directly use SQL Server for this, or even SQL-NS (see my contribution on the same at MSDN India), another solution is to use SQL-DMO to do this operation. SQL-DMO provides an object view of all SQL Server objects can be used in any COM-enabled environment. In this article, we will see how to use VB.NET and SQL-DMO to add a new trigger to an existing table.

The steps in getting the list of indexes using SQL-DMO are:

Connect to a SQL Server.
Connect to a database and to a table within the databse.
Create a new trigger object and add it to the table.

Here is an example:


Module AddTrigger
    Sub Main()
        Dim oServer As SQLDMO.SQLServer         ' Reference to a SQL Server
        Dim oDatabase As SQLDMO.Database        ' Reference to a database
        Dim oTable As SQLDMO.Table              ' Reference to a table
        Dim oTrigger As SQLDMO.Trigger          ' Reference to a trigger
        Dim strTriggerText As String

        ' Form the trigger text
        strTriggerText = _
        "CREATE TRIGGER authors_insert ON authors FOR INSERT AS " & _
        "BEGIN " & _
        "   SELECT * FROM authors " & _
        "END"

        oServer = New SQLDMO.SQLServer
        Console.WriteLine("Connecting to the server...")
        Try
            ' Connect to a specific SQL Server
            oServer.Connect("lp-srinivas", "sa", "password")

            ' Get a reference to the "pubs" database
            oDatabase = oServer.Databases.Item("pubs")

            ' Get a reference to the "auhtors" table
            oTable = oDatabase.Tables.Item("authors")

            ' Create a new trigger object
            oTrigger = New SQLDMO.Trigger

            ' Assign a name to the trigger. This name must be the same as
            ' the name given in the trigger text
            oTrigger.Name = "authors_insert"
            oTrigger.Text = strTriggerText

            ' Add the trigger to the table
            oTable.Triggers.Add(oTrigger)

            ' Cleanup all references and resources
            oServer.Application.Quit()
            Console.WriteLine("Done adding the trigger...")
        Catch ex As Exception
            Console.WriteLine("SQLDMO Error: {0}", ex.Message)
        End Try
        ' Wait for user input
        Console.ReadLine()
    End Sub
End Module

The above program will create an insert trigger on the authors table. Note that each change that we make to an object in SQL Server is automic in itself. If we want to batch a set of changes, we need to use the BeginAlter and the DoAlter methods on the appropriate object.

Home