• Article

Creating, Altering, and Removing Triggers

In SMO, triggers are represented by using the Trigger object. The Transact-SQL code that runs when the trigger that is fired is set by the TextBody property of the Trigger object. The type of trigger is set by using other properties of the Trigger object, such as the Update property. This is a Boolean property that specifies whether the trigger is fired by an UPDATE of records on the parent table.

The Trigger object represents traditional, data manipulation language (DML) triggers. In SQL Server 2008, data definition language (DDL) triggers are also supported. DDL triggers are represented by the DatabaseDdlTrigger object and the ServerDdlTrigger object.

Example

To use any code example that is provided, you will have to choose the programming environment, the programming template, and the programming language in which to create your application. For more information, see "How to: Create a Visual Basic SMO Project in Visual Studio .NET" or "How to: Create a Visual C# SMO Project in Visual Studio .NET" in SQL Server Books Online.

Creating, Altering, and Removing a Trigger in Visual Basic

This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2008R2 database. The trigger sends a reminder message when the table is updated or a new record is inserted.

'Connect to the local, default instance of SQL Server.
Dim mysrv As Server
mysrv = New Server
'Reference the AdventureWorks2008R2 database.
Dim mydb As Database
mydb = mysrv.Databases("AdventureWorks2008R2")
'Declare a Table object variable and reference the Customer table.
Dim mytab As Table
mytab = mydb.Tables("Customer", "Sales")
'Define a Trigger object variable by supplying the parent table, schema ,and name in the constructor.
Dim tr As Trigger
tr = New Trigger(mytab, "Sales")
'Set TextMode property to False, then set other properties to define the trigger.
tr.TextMode = False
tr.Insert = True
tr.Update = True
tr.InsertOrder = Agent.ActivationOrder.First
Dim stmt As String
stmt = " RAISERROR('Notify Customer Relations',16,10) "
tr.TextBody = stmt
tr.ImplementationType = ImplementationType.TransactSql
'Create the trigger on the instance of SQL Server.
tr.Create()
'Remove the trigger.
tr.Drop()

Creating, Altering, and Removing a Trigger in Visual C#

This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2008R2 database. The trigger sends a reminder message when the table is updated or a new record is inserted.

{
            //Connect to the local, default instance of SQL Server. 
            Server mysrv;
            mysrv = new Server();
            //Reference the AdventureWorks2008R2 database. 
            Database mydb;
            mydb = mysrv.Databases["AdventureWorks2008R2"];
            //Declare a Table object variable and reference the Customer table. 
            Table mytab;
            mytab = mydb.Tables["Customer", "Sales"];
            //Define a Trigger object variable by supplying the parent table, schema ,and name in the constructor. 
            Trigger tr;
            tr = new Trigger(mytab, "Sales");
            //Set TextMode property to False, then set other properties to define the trigger. 
            tr.TextMode = false;
            tr.Insert = true;
            tr.Update = true;
            tr.InsertOrder = ActivationOrder.First;
            string stmt;
            stmt = " RAISERROR('Notify Customer Relations',16,10) ";
            tr.TextBody = stmt;
            tr.ImplementationType = ImplementationType.TransactSql;
            //Create the trigger on the instance of SQL Server. 
            tr.Create();
            //Remove the trigger. 
            tr.Drop();
        }

Creating, Altering, and Removing a Trigger in PowerShell

This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2008R2 database. The trigger sends a reminder message when the table is updated or a new record is inserted.

# Set the path context to the local, default instance of SQL Server and to the
#database tables in Adventureworks2008R2
CD \sql\localhost\default\databases\AdventureWorks2008R2\Tables\

#Get reference to the trigger's target table
$mytab = get-item Sales.Customer

# Define a Trigger object variable by supplying the parent table, schema ,and name in the constructor.
$tr  = New-Object -TypeName Microsoft.SqlServer.Management.SMO.Trigger `
-argumentlist $mytab, "Sales"

# Set TextMode property to False, then set other properties to define the trigger. 
$tr.TextMode = $false
$tr.Insert = $true
$tr.Update = $true
$tr.InsertOrder = [Microsoft.SqlServer.Management.SMO.Agent.ActivationOrder]::First
$tr.TextBody = " RAISERROR('Notify Customer Relations',16,10) "
$tr.ImplementationType = [Microsoft.SqlServer.Management.SMO.ImplementationType]::TransactSql

# Create the trigger on the instance of SQL Server. 
$tr.Create()

#Remove the trigger. 
$tr.Drop()