Working with Triggers

Visual Basic

Imports System Imports System.Data Imports System.Data.Sql Imports System.Data.SqlTypes Imports Microsoft.SqlServer.Server Imports System.Data.SqlClient Partial Public Class Triggers <Microsoft.SqlServer.Server.SqlTrigger( _

Name

:="ReturnDifferenceVb", Target:="Products", Event:="FOR UPDATE")> _ Public Shared Sub ReturnDifferenceVb() Using cn As New SqlConnection() cn.ConnectionString = "context connection=true" cn.Open() Using cmd As SqlCommand = cn.CreateCommand() cmd.CommandText = _ "Select i.ProductID,(i.UnitPrice - d.UnitPrice) as AmountChanged " _ + "from INSERTED i JOIN DELETED d ON i.ProductID = d.ProductID " _ + "ORDER BY ProductID ASC" SqlContext.Pipe.ExecuteAndSend(cmd) End Using End Using End Sub End Class

C#

using System; using System.Data; using System.Data.Sql; using Microsoft.SqlServer.Server; using System.Data.SqlClient; public partial class Triggers { [Microsoft.SqlServer.Server.SqlTrigger ( Name="ReturnDifferenceCs", Target="Products", Event="FOR UPDATE")] public static void ReturnDifferenceCs() { using (SqlConnection cn = new SqlConnection()) { cn.ConnectionString = "context connection=true"; cn.Open(); using (SqlCommand cmd = cn.CreateCommand()) { cmd.CommandText = "Select i.ProductID,(i.UnitPrice - d.UnitPrice) as AmoutChanged " + "from INSERTED i JOIN DELETED d ON i.ProductID = d.ProductID " + "ORDER BY ProductID ASC"; SqlContext.Pipe.ExecuteAndSend(cmd); } } } }

Notice that the trigger template creates a partial class for all triggers to join. The SqlTrigger attribute tells Visual Studio .NET how to register this method. If you had to manually register this method, you would use the following syntax.

SQL: Trigger Registration

CREATE TRIGGER <trigger name>


ON <table name>


FOR <INSERT, UPDATE, DELETE>


AS EXTERNAL NAME <assembly name>.<trigger name>'

This example uses the context connection to access the INSERTED virtual table, which is aliased as i , and DELETED virtual tables, which is aliased as d . These tables are populated with the rows that have changed. Because this is an update trigger, there is a matching inserted row for every deleted row. The inserted and deleted rows are joined together to calculate the price change.

Add the following script to the Test.sql file to update the prices on products whose product ID is less than 5.

Visual Basic

--10% increase in prices UPDATE products SET unitprice = unitprice * 1.1 WHERE ProductID < 5

C#

--10% increase in prices UPDATE products SET unitprice = unitprice * 1.1 WHERE ProductID < 5

When you run this script, products 1 through 4 are updated, and you see the returned results in the output window, as follows . Note that because this modifies data, every time you run this script your results are different.

ProductID AmoutChanged


----------- ------------


1 1.8


2 1.9


3 1


4 2.2


(4 row(s) affected)


(4 row(s) returned)

If you wanted to add a conditional check to display the output only if the UnitPrice column data has changed, you could use the IsUpdatedColumn method on the TriggerContext property of the SqlContext class. Be aware that the IsUpdatedColumn method is similar to its T-SQL counterpart in that it requires an ordinal column position as a parameter instead of a column name. This means that adding a column might change the ordinal position of the column you are monitoring.

Transactions in Triggers

A trigger is always part of an ongoing transaction, either because you have explicitly begun a transaction or because an implicit transaction was created for you. This means you can abort the transaction to prevent the data modification. For example, you can use a trigger to perform validation. If you inspect the inserted row and the data is not valid, you can add the following statement to abort the change. Transactions are covered in more detail in the next chapter.

Visual Basic

System.Transactions.Transaction.Current.Rollback()

{% if main.adsdop %}{% include 'adsenceinline.tpl' %}{% endif %}

C#

System.Transactions.Transaction.Current.Rollback();