A trigger is a function that is executed in response to an insert, update, or delete of one or more rows from a table; it's an event handler. Coding the trigger is similar to coding a stored procedure, except you typically want to access the rows that are being inserted or deleted. Notice that I didn't mention updated rows, because an update looks like a delete of the original row and an insert of a new row with changes.
You can use a single trigger for insert, update, and delete, which makes sense if you are going to perform the same function. In most cases, it probably makes more sense to use separate triggers for each. You can also assign more than one trigger to a table, but be careful: their firing order is not guaranteed.
Like the stored procedure, the trigger is capable of sending rowsets to the client. For example, a trigger can return the product ID and the amount by which the price changed if the unit price has been updated on a product.
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.
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()
C#
System.Transactions.Transaction.Current.Rollback();