As it does with other types of routines, SQL Server 2005 allows you to use a .NET language of your choice to develop common language runtime (CLR) triggers. This capability is especially useful when you want your routines to perform activity for which T-SQL is weak at, such as complex calculations, procedural logic, or access to external resources. In this section, I will provide an example for a CLR trigger, though I have to say that I find the whole idea of developing triggers with CLR code moot.
As an example, you will be provided with a trigger that audits the data from inserted and deleted for DML triggers in the Microsoft Windows event application log. Auditing to the Windows event log gives you similar functionality as auditing to a table variable–you still have the audited information, even if the transaction is later rolled back.
When I was thinking about an example for a CLR trigger, I wanted to create a trigger that would send an e-mail at first. But then I realized this would be a bad example. Remember that a trigger is part of the transaction, and resources are locked during the transaction. Depending on the action issued and isolation level used, locks can be held until the end of the transaction. Sending e-mail can take a while. Also, if you want to send an e-mail from a trigger, you should also be prepared for different kinds of problems–for example, an SMTP of MAPI server could be stopped. The local event log should always be available or otherwise full, and writing to it is faster than sending e-mail. Keep in mind though, that writing to the local event log may still cause performance problems.
Note the importance of the previous paragraph. Because a trigger is part of a transaction, you should be very careful with the code you issue from the trigger; it should always run as fast as possible. I prefer to have only Transact-SQL statements in the body of a trigger. This way you have more control and you can test the performance easier. Imagine there is a performance bottleneck somewhere in the CLR trigger code, and you are a DBA without enough CLR programming knowledge, or you simply don’t have access to the code. There is not much that you can do.
Possible performance problems are not the only drawback of auditing in the local event log. The event log can get full, and the auditing trigger would not be able to write to it in such a case, so it would roll back the transaction. You can manually clear the log, but until the log is cleared you would not be able to modify the data. Another possibility would be that the trigger would clear the log, but then what’s the purpose of auditing, if you simply recycle the previously audited information? I tried to mitigate this problem by limiting the auditing info to the first 200 characters only. Even this solution doesn’t make much sense, because you could simply use the T-SQL command RAISERROR ... WITH LOG, which can accomplish the same task easily–the errors logged with RAISERROR are currently limited to 440 bytes. Writing to the event log, whether you use RAISERROR or CLR code, needs permissions of quite a high level.
In short, my advice would be to avoid developing triggers with CLR code. Still, I wanted to provide an example for a CLR trigger to demonstrate the technicalities involved, and to discuss the drawbacks of doing so.
The trigger writes to a resource external to the database. In Chapter 7, I explained how to set up the environment to allow external access to assemblies, and the security and stability issues involved. Make sure you go over that section first if you haven’t done so already. To allow the trigger that is described in this section to run, you have two options:
The less secure and therefore less recommended option is to set the TRUSTWORTHY option of the database to ON, and to create (or alter) the assembly with the EXTERNAL_ACCESS permission set (UNSAFE would be needed to write to a remote machine event log):
-- Database option TRUSTWORTHY needs to be ON for EXTERNAL_ACCESS ALTER DATABASE CLRUtilities SET TRUSTWORTHY ON; GO -- Alter assembly with PERMISSION_SET = EXTERNAL_ACCESS ALTER ASSEMBLY CLRUtilities WITH PERMISSION_SET = EXTERNAL_ACCESS;
As mentioned in Chapter 7, the more secure option is to sign the assembly with a strong name key file. Then run the following code to create an asymmetric key from the executable .dll file and a corresponding login with the EXTERNAL ACCESS (or UNSAFE) ASSEMBLY permission:
USE master; GO CREATE ASYMMETRIC KEY CLRUtilitiesKey FROM EXECUTABLE FILE = 'C:CLRUtilitiesCLRUtilitiesinDebugCLRUtilities.dll'; -- Create login and grant it with unsafe permission level CREATE LOGIN CLRUtilitiesLogin FROM ASYMMETRIC KEY CLRUtilitiesKey GRANT EXTERNAL ACCESS ASSEMBLY TO CLRUtilitiesLogin;
CLR code inside SQL Server is always invoked in the context of the process account. If you would like to use the calling user’s identity instead for your CLR code that performs an action outside SQL Server, you have to obtain an impersonation token through the WindowsIdentity property of the SqlContext object. The WindowsIdentity property returns a WindowsIdentity object instance. It represents the Windows (OS) identity of the caller (or null if the client was authenticated using SQL Server Authentication). To be able to access this property, an assembly has to be marked with the EXTERNAL_ACCESS or UNSAFE permission set.
You should be aware of all implications of setting the permission set for the assembly to EXTERNAL_ACCESS or UNSAFE and setting the database option THRUSTWORTHY to ON. Going deeper with security would be outside the scope of this book, but just be aware: the UNSAFE permission set allows assemblies unrestricted access to resources, both within and outside SQL Server, and also calling unmanaged code. From the example in this section, you can learn the technicalities of creating CLR triggers, but you can also get an impression of the implications of using CLR code inside a database imprudently.
Remember that Appendix A provides the instructions required to develop, build, deploy, and test your .NET code. Here you will be provided with the trigger’s code, including explanations, and instructions to register the trigger in the database and test it.
Example 8-13 has the C# code that defines the trg_GenericDMLAudit trigger.
Example 8-13. C# code for trg_GenericDMLAudit trigger
// Generic trigger for auditing DML statements
// trigger will write first 200 characters from all columns
// in an XML format to App Event Log
[SqlTrigger(Name = @"trg_GenericDMLAudit", Target = "T1",
Event = "FOR INSERT, UPDATE, DELETE")]
public static void trg_GenericDMLAudit()
{
// Get the trigger context to get info about the action type
SqlTriggerContext triggContext = SqlContext.TriggerContext;
// Prepare the command and pipe objects
SqlCommand command;
SqlPipe pipe = SqlContext.Pipe;
// Check whether the action is Insert
switch (triggContext.TriggerAction)
{
case TriggerAction.Insert:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type, cast it
// to nvarchar and select only a substring from it
// Info from Inserted
command = new SqlCommand(
@"SELECT 'New data: '
+ SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX))
,1,200) AS InsertedContents200
FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE)
AS a(InsertedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
// Note: if the following line would use
// Environment.MachineName instead of "." to refer to
// the local machine event log, the assembly would need
// the UNSAFE permission set
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
case TriggerAction.Update:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type,
// cast it to nvarchar and select only a substring from it
// Info from Deleted
command = new SqlCommand(
@"SELECT 'Old data: '
+ SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX))
,1,200) AS DeletedContents200
FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE)
AS a(DeletedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Info from Inserted
command.CommandText =
@"SELECT ' // New data: '
+ SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX))
,1,200) AS InsertedContents200
FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE)
AS a(InsertedContents);";
msg = msg + (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
case TriggerAction.Delete:
// Retrieve the connection that the trigger is using
using (SqlConnection connection
= new SqlConnection(@"context connection=true"))
{
connection.Open();
// Collect all columns into an XML type,
// cast it to nvarchar and select only a substring from it
// Info from Deleted
command = new SqlCommand(
@"SELECT 'Old data: '
+ SUBSTRING(CAST(a. DeletedContents AS NVARCHAR(MAX))
,1,200) AS DeletedContents200
FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE)
AS a(DeletedContents);",
connection);
// Store info collected to a string variable
string msg;
msg = (string)command.ExecuteScalar();
// Write the audit info to the event log
EventLogEntryType entry = new EventLogEntryType();
entry = EventLogEntryType.SuccessAudit;
EventLog ev = new EventLog(@"Application",
".", @"GenericDMLAudit Trigger");
ev.WriteEntry(msg, entry);
// send the audit info to the user
pipe.Send(msg);
}
break;
default:
// Just to be sure - this part should never fire
pipe.Send(@"Nothing happened");
break;
}
}Note that the header of the trigger in Example 8-13 contains the attribute Target = ″T1″. This attribute specifies the table on which you want to define the trigger. It is required only when deploying the trigger automatically from Microsoft Visual Studio. The trigger is written as a generic trigger that can be attached to any table. You can remove this attribute and use manual registration of the trigger to attach it to any table you like.
Using the SqlContext object, you can get the context of the current caller. The trigger’s code uses a context connection, which should be familiar to you by now from the coverage of CLR stored procedures in the previous chapter. This is good enough for stored procedures, but inside a trigger you need a context in a much finer-grained level. You can gain the required context via the SqlContext.TriggerContext object (SqlTriggerContext class). This object provides context information about the trigger. This information includes the type of the DML action that caused the trigger to fire and which columns were modified if an UPDATE statement was issued. If this is a DDL trigger, you can get information about the event that fired the trigger from an XML EventData structure.
The trigger uses the EventLogEntryType enumeration and EventLog class from the System. Diagnostics namespace. It is also important that the trigger uses "." to refer to the current machine (remember that you want to write the auditing information to the local machine’s event log). If it would use the Environment class from the System namespace to get the current machine name (Environment.MachineName), then the assembly would need the UNSAFE permission set. I have to thank Nicole Calinoiu, an extremely knowledgeable and friendly Visual Developer – Security MVP from Montreal, Canada, for pointing out this detail to me.
A small trick is used in the T-SQL part of the code embedded in the trigger (in the CommandText property of the SqlCommand object). If you want to create a generic trigger that can be used with any table, you don’t want to specify an explicit column list from inserted or deleted. Also, you need to collect information from all columns of all possible data types to generate a result string, because you write messages to the event log as strings. So the trigger’s code uses SELECT * to get all the columns no matter what the structure of the table is, although generally using SELECT * is a bad practice. It also uses the FOR XML clause to convert all information to XML in the inner SELECT (in the derived table). You can use the FOR XML clause in the derived table because it returns a table result. This is achieved by using the new TYPE directive of the FOR XML clause. You get back a table with a single column of the XML data type; without the TYPE directive, you would get the result in textual form. The code converts the value of the XML type column to the NVARCHAR type in the outer query and writes the first 200 characters to the event log.
Example 8-14 shows the Microsoft Visual Basic code that defines the trg_GenericDMLAudit trigger.
Example 8-14. Visual Basic code for trg_GenericDMLAudit trigger
' Generic trigger for auditing DML statements
' trigger will write first 200 characters from all columns
' in an XML format to App Event Log
<SqlTrigger(Name:="trg_GenericDMLAudit", Target:="T1", _
Event:="FOR INSERT, UPDATE, DELETE")> _
Public Shared Sub trg_GenericDMLAudit()
' Get the trigger context to get info about the action type
Dim triggContext As SqlTriggerContext = SqlContext.TriggerContext
' Prepare the command and pipe objects
Dim command As SqlCommand
Dim pipe As SqlPipe = SqlContext.Pipe
' Check whether the action is Insert
Select Case triggContext.TriggerAction
Case TriggerAction.Insert
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Inserted
command = New SqlCommand( _
"SELECT 'New data: ' + " & _
"SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX)" & _
"),1,200) AS InsertedContents200 " & _
"FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE) " & _
"AS a(InsertedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
' Note: if the following line would use
' Environment.MachineName instead of "." to refer to
' the local machine event log, the assembly would need
' the UNSAFE permission set
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case TriggerAction.Update
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Deleted
command = New SqlCommand( _
"SELECT 'Old data: ' + " & _
"SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX)" & _
"),1,200) AS DeletedContents200 " & _
"FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE) " & _
"AS a(DeletedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Info from Inserted
command.CommandText = _
"SELECT ' // New data: ' + " & _
"SUBSTRING(CAST(a.InsertedContents AS NVARCHAR(MAX)" & _
"),1,200) AS InsertedContents200 " & _
"FROM (SELECT * FROM Inserted FOR XML AUTO, TYPE) " & _
"AS a(InsertedContents);"
msg = msg + CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case TriggerAction.Delete
' Retrieve the connection that the trigger is using
Using connection _
As New SqlConnection("Context connection = true")
connection.Open()
' Collect all columns into an XML type,
' cast it to nvarchar and select only a substring from it
' Info from Deleted
command = New SqlCommand( _
"SELECT 'Old data: ' + " & _
"SUBSTRING(CAST(a.DeletedContents AS NVARCHAR(MAX)" & _
"),1,200) AS DeletedContents200 " & _
"FROM (SELECT * FROM Deleted FOR XML AUTO, TYPE) " & _
"AS a(DeletedContents);", _
connection)
' Store info collected to a string variable
Dim msg As String
msg = CStr(command.ExecuteScalar())
' Write the audit info to the event log
Dim entry As EventLogEntryType
entry = EventLogEntryType.SuccessAudit
Dim ev As New EventLog("Application", _
".", "GenericDMLAudit Trigger")
ev.WriteEntry(msg, entry)
' send the audit info to the user
pipe.Send(msg)
End Using
Case Else
' Just to be sure - this part should never fire
pipe.Send("Nothing happened")
End Select
End SubRun the following code to create a test table called T1:
USE ClrUtilities;
GO
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;
GO
CREATE TABLE dbo.T1
(
keycol INT NOT NULL PRIMARY KEY,
datacol VARCHAR(10) NOT NULL
);Use the following code to register the C# version of the trigger in the database and attach it to the table T1:
CREATE TRIGGER trg_T1_iud_GenericDMLAudit ON dbo.T1 FOR INSERT, UPDATE, DELETE AS EXTERNAL NAME CLRUtilities.CLRUtilities.trg_GenericDMLAudit;
Use the following code to register the trigger if you used Visual Basic to develop it:
CREATE TRIGGER trg_T1_iud_GenericDMLAudit ON dbo.T1 FOR INSERT, UPDATE, DELETE AS EXTERNAL NAME CLRUtilities.[CLRUtilities.CLRUtilities].trg_GenericDMLAudit;
Issue the following modifications against T1:
INSERT INTO dbo.T1(keycol, datacol) VALUES(1, N'A'), UPDATE dbo.T1 SET datacol = N'B' WHERE keycol = 1; DELETE FROM dbo.T1 WHERE keycol = 1;
The trigger will produce the following output:
New data: <Inserted keycol="1" datacol="A"/> Old data: <Deleted keycol="1" datacol="A"/> // New data: <Inserted keycol="1" datacol="B"/> Old data: <Deleted keycol="1" datacol="B"/>
Tip
You can clear the root namespace in a Visual Basic project, and this way the T-SQL code required to create/alter assemblies and register routines would be the same for Visual Basic and C# assemblies. To clear the root namespace, in Visual Studio select the Project | Properties menu item, go to the Application tab, and clear the "Root namespace" text box.
If you examine the Windows application log, you will find that the changes were audited there as well, as shown in Figure 8-1.
If you don’t see the events in the Windows application log, make sure that it’s not full or that you allow recycling it.
When you’re done, run the following code for cleanup:
IF OBJECT_ID('dbo.T1') IS NOT NULL
DROP TABLE dbo.T1;