創建、更改和刪除觸發器Creating, Altering, and Removing Triggers

08/06/2017

本文內容

適用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 數據庫Azure SQL DatabaseAzure SQL 數據庫Azure SQL Database Azure SQL 托管實例Azure SQL Managed InstanceAzure SQL 托管實例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics適用于：Applies to: SQL ServerSQL Server(所有支持的版本)SQL ServerSQL Server (all supported versions) Azure SQL 數據庫Azure SQL DatabaseAzure SQL 數據庫Azure SQL Database Azure SQL 托管實例Azure SQL Managed InstanceAzure SQL 托管實例Azure SQL Managed Instance Azure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse AnalyticsAzure Synapse Analytics

在 SMO 中，觸發器由 Trigger 對象表示。In SMO, triggers are represented by using the Trigger object. Transact-SQLTransact-SQL觸發器對象的屬性設置所激發的觸發器時運行的代碼 TextBody 。The Transact-SQLTransact-SQL code that runs when the trigger that is fired is set by the TextBody property of the Trigger object. 使用 Trigger 對象的其他屬性(如 Update 屬性)可以設置觸發器的類型。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.

Trigger 對象表示傳統的數據操作語言 (DML) 觸發器。The Trigger object represents traditional, data manipulation language (DML) triggers. 在 SQL Server 2008SQL Server 2008 和更改版本中，也同樣支持數據定義語言 (DDL) 觸發器。In SQL Server 2008SQL Server 2008 and later versions, 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.

在 Visual Basic 中創建、更改和刪除觸發器Creating, Altering, and Removing a Trigger in Visual Basic

此代碼示例演示如何在 AdventureWorks2012AdventureWorks2012 數據庫中名為 Sales 的現有表上創建并插入更新觸發器。This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2012AdventureWorks2012 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()

在 Visual C# 中創建、更改和刪除觸發器Creating, Altering, and Removing a Trigger in Visual C#

此代碼示例演示如何在 AdventureWorks2012AdventureWorks2012 數據庫中名為 Sales 的現有表上創建并插入更新觸發器。This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2012AdventureWorks2012 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();

}

在 PowerShell 中創建、更改和刪除觸發器Creating, Altering, and Removing a Trigger in PowerShell

此代碼示例演示如何在 AdventureWorks2012AdventureWorks2012 數據庫中名為 Sales 的現有表上創建并插入更新觸發器。This code example shows how to create and insert an update trigger on an existing table, named Sales, in the AdventureWorks2012AdventureWorks2012 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()

總結

以上是生活随笔為你收集整理的mysql2005触发器修改成绩_创建、更改和删除触发器的全部內容，希望文章能夠幫你解決所遇到的問題。

如果覺得生活随笔網站內容還不錯，歡迎將生活随笔推薦給好友。