Professional SQL Server™ 2005 Programming

14.8. Creating Triggers from Assemblies

Much like the other assembly types we've worked with so far in this chapter, triggers have a lot in common with the rest, but also their own little smattering of special things.

The differences will probably come to mind quickly if you think about it for any length of time:

• How do we deal with the contextual nature of triggers? That is, how do we know to handle things differently if it's an INSERT trigger situation versus a DELETE or UPDATE trigger?

• How do we access the inserted and deleted tables?

You may recall from earlier examples, how we can obtain the "context" of the current connection — it is by utilizing this context that we are able to gain access to different objects that we are interested in. For example, the SqlContext object that we've obtained a connection from in prior examples also contains a SqlTriggerContext object — we can use that to get properties such as whether we are dealing with an insert, update, or delete scenario (thefirst question we had). The fact that we have access to the current connections also implies that we are able to access the inserted and deleted tables simply by querying them. Let's get right to putting this to use in an example.

Start by creating a new SQL Server project in Visual Studio (I've called mine ExampleTrigger this time). Once your project is up, right-click the project in the solution explorer and select AddTrigger....

Visual Studio is nice enough to provide you with what is, for the ...