creating-altering-and-removing-triggers.md

title	Creating, Altering, and Removing Triggers
ms.custom	seo-dt-2019
ms.date	08/06/2017
ms.prod	sql
ms.prod_service	database-engine
ms.reviewer
ms.technology
ms.topic	reference
helpviewer_keywords	triggers [SMO]
ms.assetid	8ddbe23b-6e31-4f8e-8a70-17bd5072413e
author	markingmyname
ms.author	maghan
monikerRange	=azuresqldb-current||=azure-sqldw-latest||>=sql-server-2016||=sqlallproducts-allversions||>=sql-server-linux-2017||=azuresqldb-mi-current

Creating, Altering, and Removing Triggers

[!INCLUDEappliesto-ss-asdb-asdw-xxx-md] In SMO, triggers are represented by using the xref:Microsoft.SqlServer.Management.Smo.Trigger object. The [!INCLUDEtsql] code that runs when the trigger that is fired is set by the xref:Microsoft.SqlServer.Management.Smo.Trigger.TextBody%2A property of the Trigger object. The type of trigger is set by using other properties of the xref:Microsoft.SqlServer.Management.Smo.Trigger object, such as the xref:Microsoft.SqlServer.Management.Smo.Trigger.Update%2A property. This is a Boolean property that specifies whether the trigger is fired by an UPDATE of records on the parent table.

The xref:Microsoft.SqlServer.Management.Smo.Trigger object represents traditional, data manipulation language (DML) triggers. In [!INCLUDEssKatmai] and later versions, data definition language (DDL) triggers are also supported. DDL triggers are represented by the xref:Microsoft.SqlServer.Management.Smo.DatabaseDdlTrigger object and the xref:Microsoft.SqlServer.Management.Smo.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 Create a Visual C# SMO Project in Visual Studio .NET.

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 [!INCLUDEssSampleDBnormal] 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 AdventureWorks2012 2008R2 database.
Dim mydb As Database
mydb = mysrv.Databases("AdventureWorks2012")
'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 [!INCLUDEssSampleDBnormal] 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 AdventureWorks2012 database.   
            Database mydb;  
            mydb = mysrv.Databases["AdventureWorks2012"];  
            //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 [!INCLUDEssSampleDBnormal] 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 Adventureworks2012  
CD \sql\localhost\default\databases\AdventureWorks2012\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()