ADO.NET 3.5 Cookbook, 2nd Edition

12.6. Creating a DML Trigger

Problem

You need to create a CLR DML trigger.

Solution

The solution uses two tables named Volume and VolumeAudit. Execute the following T-SQL batch to create the tables:

	USE AdoDotNet35Cookbook
	GO

	CREATE TABLE Volume
	(
	    ID int NOT NULL,
	    Length float NOT NULL,
	    Width float NOT NULL,
	    Height float NOT NULL,
	    Volume float NOT NULL CONSTRAINT DF_Area_Area DEFAULT ((0)),
	    CONSTRAINT PK_Volume PRIMARY KEY CLUSTERED
	    (
	        ID ASC
	    )
	)
	GO

	CREATE TABLE VolumeAudit
	(
	    Action varchar(50) NOT NULL,
	    Description varchar(max) NOT NULL
	)

The solution creates update, insert, and delete DML triggers that log updates, inserts, and deletes to a table named Volume. These events are logged to a table named VolumeAudit. The example then registers the triggers and shows the results of executing DML statements against the Volume table. Follow these steps:

1. Create a new SQL Server project in Visual Studio and name it ClrDmlTrigger.

2. Create a trigger item in the project. Name the item VolumeTriggers.cs.

3. The C# code in VolumeTriggers.cs in the project ClrDmlTrigger is shown in Example 12-9.

   Example 12-9. File: VolumeTriggers.cs

   using System; using System.Data; using System.Data.SqlClient; using Microsoft.SqlServer.Server; public partial class Triggers { [SqlTrigger(Target = "Volume", Event = "FOR INSERT")] public static void InsertTrigger() { using (SqlConnection conn = new SqlConnection("context connection=true")) { SqlDataAdapter da =new SqlDataAdapter("SELECT * FROM INSERTED", conn); DataTable dt ...