Working with Triggers

 

Working with Triggers

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.

image from book

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 
image from book

image from book

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);          }       }    } } 
image from book

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.

image from book

SQL: Trigger Registration

CREATE TRIGGER <trigger name> ON <table name> FOR <INSERT, UPDATE, DELETE> AS EXTERNAL NAME <assembly name>.<trigger name>' 
image from book

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.

image from book

Visual Basic

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

image from book

C#

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

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.

image from book

Visual Basic

System.Transactions.Transaction.Current.Rollback() 
image from book

image from book

C#

System.Transactions.Transaction.Current.Rollback(); 
image from book

 


Programming Microsoft ADO. NET 2.0 Applications. Advanced Topics
Linux Application Development (2nd Edition)
ISBN: 735621411
EAN: 2147483647
Year: 2004
Pages: 85
Authors: Michael K. Johnson, Erik W. Troan
BUY ON AMAZON

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net