Data Points
Creating Audit Tables, Invoking COM Objects, and More
John Papa
Code download available at:
DataPoints0404.exe
(111 KB)
Browse the Code Online
Dealing with error handling between T-SQL and a calling application, evaluating when a field's value has changed, and creating auditing tables in SQL Server™ are all common issues that developers must tackle. This month's column will address these and other development scenarios based on some of my most frequently received T-SQL questions. Sample code is included in the download file for many of the following questions including code to send an e-mail from a stored procedure by invoking a COM object.
Q I have a table for which I want to track both the date and time each record was created and the last date and time that any field in the row was updated. I often need to update several rows at once, so my solution has to work for one or more rows. How can I accomplish this using features in SQL Server?
Q I have a table for which I want to track both the date and time each record was created and the last date and time that any field in the row was updated. I often need to update several rows at once, so my solution has to work for one or more rows. How can I accomplish this using features in SQL Server?
A This issue can be resolved using a handful of features in SQL Server including a DEFAULT constraint, the built-in GETDATE function, and an AFTER UPDATE trigger. To set a value indicating when a record was created, a DEFAULT constraint can be applied to the appropriate column using the default value from the GETDATE function. A column that has a DEFAULT constraint does not need to be included in an INSERT statement since it can use the default value. This means that if you add a column to represent the date and time a record was created, you can do so without modifying any existing INSERT statements written against the table (as long as those statements include a column list). Using a DEFAULT constraint to assign the current date and time using the GETDATE function works whether 1 row or 100 rows are inserted. So to accomplish the first part of this task a column named dtCreated could be added to an existing table named tblCustomer. The code in Figure 1 shows the SQL Data Definition Language (DDL) used to create tblCustomer, its primary key constraint, and the new column called dtCreated. Theoretically, however, someone could still pass an explicit value for the dtCreated column. This would mean that the default value would be overwritten and the creation data might be incorrect. If you want to force this time stamp to always be the current value, an insert trigger can be used that always sets the value to GETDATE even if a specific value was in the insert list.
A This issue can be resolved using a handful of features in SQL Server including a DEFAULT constraint, the built-in GETDATE function, and an AFTER UPDATE trigger. To set a value indicating when a record was created, a DEFAULT constraint can be applied to the appropriate column using the default value from the GETDATE function. A column that has a DEFAULT constraint does not need to be included in an INSERT statement since it can use the default value. This means that if you add a column to represent the date and time a record was created, you can do so without modifying any existing INSERT statements written against the table (as long as those statements include a column list). Using a DEFAULT constraint to assign the current date and time using the GETDATE function works whether 1 row or 100 rows are inserted. So to accomplish the first part of this task a column named dtCreated could be added to an existing table named tblCustomer. The code in Figure 1 shows the SQL Data Definition Language (DDL) used to create tblCustomer, its primary key constraint, and the new column called dtCreated. Theoretically, however, someone could still pass an explicit value for the dtCreated column. This would mean that the default value would be overwritten and the creation data might be incorrect. If you want to force this time stamp to always be the current value, an insert trigger can be used that always sets the value to GETDATE even if a specific value was in the insert list.
Figure 1 Creating tblCustomer and dtCreated
|
CREATE TABLE tblCustomer
(
nCustomerID INT IDENTITY,
sCustomerName VARCHAR(50),
sAddress VARCHAR(50),
sCity VARCHAR(50),
sState CHAR(2),
sZip CHAR(5)
)
GO
ALTER TABLE tblCustomer ADD PRIMARY KEY (nCustomerID)
GO
ALTER TABLE tblCustomer ADD dtCreated DATETIME DEFAULT GETDATE()
GO
|
The second part of this task cannot be addressed with a DEFAULT constraint, but triggers will do the trick. First, another column should be added to the tblCustomer table to store the last date and time a record was modified. The following code will add the column named dtLastUpdated to the table:
|
ALTER TABLE tblCustomer
ADD dtLastUpdated DATETIME NULL
GO
|
The key here is to make sure that for every row that is updated via a SQL UPDATE statement, its corresponding dtLastUpdated column's value is set to the current date and time. An AFTER UPDATE trigger could be created on the tblCustomer table to trap the date and time the modifications are made to the table. Then, a cursor could be created to traverse the rows that were modified. These rows are stored in the special table called INSERTED. (For more information on the inserted and deleted tables, refer to my
December 2003 column.) The cursor could be looped through and each record could be matched to the corresponding record in the tblCustomer table and then updated. While this technique will work, it does cause an UPDATE statement to be executed for every row that was originally updated. Thus, if there were 100 rows in the tblCustomer table modified by an UPDATE statement, then the cursor would loop through all 100 rows that were modified and issue 100 individual UPDATE statements to set the value in the dtLastUpdated column. Not a good thing to do.
There is a better and easier way to tackle this problem still using an AFTER UPDATE trigger. Instead of using a cursor, which can be inefficient, you can use a variation of an UPDATE statement. The following UPDATE statement will set the dtLastUpdated column's value for each modified row. It does this by joining the base table to the INSERTED table, causing a single UPDATE statement to execute inside of the trigger:
|
CREATE TRIGGER trUpd_tblCustomer ON tblCustomer AFTER UPDATE
AS
UPDATE tblCustomer SET dtLastUpdated = GETDATE()
FROM inserted i INNER JOIN tblCustomer c ON i.nCustomerID =
c.nCustomerID
GO
|
You might wonder if, by updating the tblCustomer table inside of an AFTER UPDATE trigger on the same table, it will call itself recursively. Triggers don't do that unless the RECURSIVE_TRIGGERS option is turned on. When this setting is off, direct recursion (for example, when tblCustomer updates tblCustomer in its own AFTER UPDATE trigger) will not occur. You can use the sample code in Figure 2 to test out this solution.
Figure 2 Some Inserts
|
INSERT INTO tblCustomer
(sCustomerName, sAddress, sCity, sState, sZip)
VALUES ( 'Company X', '1 Lois Lane', 'Metropolis', 'NY', '55555')
INSERT INTO tblCustomer
(sCustomerName, sAddress, sCity, sState, sZip)
VALUES ( 'Company Y', '2 Somewhere Drive', 'Anytown', 'NY', '44444')
INSERT INTO tblCustomer
(sCustomerName, sAddress, sCity, sState, sZip)
VALUES ( 'Company Z', '3 Nowhere Street', 'Who Knows', 'NC', '33333')
UPDATE tblCustomer SET sState = 'FL' WHERE sState = 'NY'
SELECT * FROM tblCustomer
|
Q When using the UPDATE function in a trigger to evaluate if a column's value has changed, how does the function compare values to determine if the field was indeed updated? Does the UPDATE function return true if the value updated merely exists in the originating UPDATE statement or does it truly check to see if the value has changed? Is there any other way I can detect if a column's value has actually changed?
Q When using the UPDATE function in a trigger to evaluate if a column's value has changed, how does the function compare values to determine if the field was indeed updated? Does the UPDATE function return true if the value updated merely exists in the originating UPDATE statement or does it truly check to see if the value has changed? Is there any other way I can detect if a column's value has actually changed?
A The UPDATE function does not actually check to see if a column value is different. Specifically, the UPDATE function can only be used inside a trigger. It accepts a single parameter that represents a column in a table and it returns a bit value to indicate whether the column's value has been involved in an update (1 for modified, 0 otherwise). However, the UPDATE function evaluates to 1 even if the column was updated but its new value is not different. For example, the following UPDATE statement issued against Northwind sets the FirstName field from Nancy to Nancy:
|
UPDATE employees SET FirstName = 'Nancy' WHERE EmployeeID = 1
|
A The UPDATE function does not actually check to see if a column value is different. Specifically, the UPDATE function can only be used inside a trigger. It accepts a single parameter that represents a column in a table and it returns a bit value to indicate whether the column's value has been involved in an update (1 for modified, 0 otherwise). However, the UPDATE function evaluates to 1 even if the column was updated but its new value is not different. For example, the following UPDATE statement issued against Northwind sets the FirstName field from Nancy to Nancy:
|
UPDATE employees SET FirstName = 'Nancy' WHERE EmployeeID = 1
|
While this statement effectively does not change the net result of what is stored in the row, it does change the value from Nancy to Nancy. Assuming there is an AFTER UPDATE trigger on this table, it would then execute:
|
CREATE TRIGGER trU_Employees_UpdateTest ON Employees AFTER UPDATE
AS
IF UPDATE(FirstName)
SELECT 'Update Evaluated to True'
GO
|
This AFTER UPDATE trigger would fire and the UPDATE(FirstName) expression would evaluate to 1, even though the name has the same value before and after. You can test this out easily enough by running the previous UPDATE statement. It should return the string value of Update Evaluated to True in this case.
But what if you want to compare the old and new values? Well, in that case the UPDATE function alone just won't cut it. However, you could compare the old and new values by using the special INSERTED table and DELETED tables inside the trigger. The trigger I just showed could be modified to accomplish this fairly easily. I would still use the UPDATE function to check if the FirstName column was in the UPDATE statement or not. It would also add some logic to compare the values in the inserted and deleted tables, as follows:
|
ALTER TRIGGER trU_Employees_UpdateTest ON Employees AFTER UPDATE
AS
IF UPDATE(FirstName)
IF EXISTS(
SELECT i.EmployeeID
FROM inserted i INNER JOIN deleted d ON i.EmployeeID =
d.EmployeeID
WHERE i.FirstName <> d.FirstName
)
SELECT 'Update Evaluated to True'
GO
|
This is less efficient than just using the UPDATE function, so you should think about whether or not the extra precision is necessary. This code makes sure that at least one of the rows that was updated had its FirstName column updated to a new value. Note that in a production environment, you would not want to put SELECT statements in a trigger. Rather, you would only want to perform "silent" actions, such as updating another table.
Q I have several ActiveX® components running on my SQL Server database server. These components are very extensive and contain many business rules that some of my stored procedures need to invoke. I know that you can call a stored procedure from an ActiveX DLL, but is there any way I can call an ActiveX component from my stored procedure?
Q I have several ActiveX® components running on my SQL Server database server. These components are very extensive and contain many business rules that some of my stored procedures need to invoke. I know that you can call a stored procedure from an ActiveX DLL, but is there any way I can call an ActiveX component from my stored procedure?
A There are a few system stored procedures in SQL Server that give developers the ability to instantiate an ActiveX component/COM object, call its methods and properties, destroy it, and even retrieve information about any exceptions that might occur. Calling a COM object from T-SQL starts with sp_OACreate. This stored procedure creates an instance of the object specified by the progid parameter and returns an integer value via the objecttoken output parameter. The objecttoken returns a handle to the COM object that can be used in the remaining sp_OA stored procedures to reference the COM object. The following code sample shows how to instantiate a COM object with sp_OACreate:
|
EXECUTE @nOLEResult = sp_OACreate 'CDO.Message', @nCDO OUT
|
A There are a few system stored procedures in SQL Server that give developers the ability to instantiate an ActiveX component/COM object, call its methods and properties, destroy it, and even retrieve information about any exceptions that might occur. Calling a COM object from T-SQL starts with sp_OACreate. This stored procedure creates an instance of the object specified by the progid parameter and returns an integer value via the objecttoken output parameter. The objecttoken returns a handle to the COM object that can be used in the remaining sp_OA stored procedures to reference the COM object. The following code sample shows how to instantiate a COM object with sp_OACreate:
|
EXECUTE @nOLEResult = sp_OACreate 'CDO.Message', @nCDO OUT
|
The sp_OACreate stored procedure also returns an integer value, which represents whether an error occurred within the COM object. A nonzero value indicates that an error has occurred. The error information can be retrieved via the sp_OAGetErrorInfo stored procedure after each call. The following code sample executes the sp_OAGetErrorInfo stored procedure, passing to it the reference to the COM object (@nCDO) and retrieving the error's source and description information via output parameters:
|
EXECUTE sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error creating object ' + @sProgID + '. Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
|
This information can be used to format an error message that could be bubbled up to an application or to a user. Error handling is crucial in most applications and while T-SQL does provide for error-handling techniques, they are less sophisticated than what more robust languages such as Visual Basic®, Visual Basic .NET, or C# offer. It is important to provide for and thoroughly test the error conditions inside of a T-SQL batch. The previous code sample demonstrates how to trap the errors from the COM object and raise them using the RAISERROR statement.
As a test, I intentionally caused an error by trying to instantiate a nonexistent COM object called CDO.Message123. The preceding error handling T-SQL logic returned the following error:
|
Server: Msg 50000, Level 16, State 1, Procedure prSendMail, Line 25
Error creating object CDO.Message123. Error # = -2147221005.
Description = Invalid class string
|
Just as their names suggest, the sp_OASetProperty and sp_OAGetProperty stored procedures provide the means to retrieve and set public properties of a COM object. Further, the sp_OAMethod stored procedure provides a way to invoke a method of a COM object. Once you're finished using an object, you call sp_OADestroy to release it. Objects that aren't explicitly freed are automatically released at the end of the T-SQL batch. The sample code in Figure 3 demonstrates these techniques through a sample stored procedure called prSendMail. This stored procedure invokes the CDO.Message object and sets its To, From, Subject, and TextBody properties before ultimately sending an e-mail message using the CDO component.
Figure 3 Invoking COM Objects and Sending E-mail Using T-SQL
|
ALTER PROCEDURE prSendMail
@sTo VARCHAR(500),
@sBody VARCHAR(8000),
@sSubject VARCHAR(500),
@sFrom VARCHAR(500)
AS
DECLARE @nCDO INT,
@nOLEResult INT,
@nOutput INT,
@sSource VARCHAR(255),
@sDescription VARCHAR(255),
@sProgID VARCHAR(50),
@sMsg VARCHAR(250)
SET @sProgID = 'CDO.Message'
EXECUTE @nOLEResult = sp_OACreate @sProgID, @nCDO OUT
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error creating object ' + @sProgID +
'. Error # = ' + CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'To', @sTo
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error executing CDO.To property. Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'From', @sFrom
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error executing CDO.From property. Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'Subject', @sSubject
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error executing CDO.Subject property. Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
EXECUTE @nOLEResult = sp_OASetProperty @nCDO, 'TextBody', @sBody
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error executing CDO.TextBody property. Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
—Call Send method of the object
EXECUTE @nOLEResult = sp_OAMethod @nCDO, 'Send', Null
IF @nOLEResult <> 0
BEGIN
EXEC sp_OAGetErrorInfo @nCDO, @sSource OUT, @sDescription OUT
SET @sMsg = 'Error executing CDO.Send method . Error # = ' +
CAST(@nOLEResult AS VARCHAR(20)) +
'. Description = ' + @sDescription
RAISERROR (@sMsg, 16, 1)
RETURN
END
—Destroy CDO
EXECUTE @nOLEResult = sp_OADestroy @nCDO
RETURN @nOLEResult
GO
|
As you can see, these system stored procedures are pretty straightforward to use. But calling COM objects from T-SQL is not reserved merely for third-party or Microsoft components such as CDOSYS, ADODB, and SQLDMO. You can also use these techniques to instantiate valid custom COM components. However, while the sp-OA* procedures are easy to use, you need to be very careful with them. Since sp-OA* procedures execute within the SQL Server memory space, improper usage could result in memory problems in your database. There are several Knowledge Base articles on the subject that go into more detail.
Q I have created an audit table to track changes to a primary table. After every change to the primary table I want to insert a row to the audit table to track the change. I could accomplish this using triggers, stored procedures, or even a business application written in Visual Basic. What are the pros and cons of these methods and which do you recommend?
Q I have created an audit table to track changes to a primary table. After every change to the primary table I want to insert a row to the audit table to track the change. I could accomplish this using triggers, stored procedures, or even a business application written in Visual Basic. What are the pros and cons of these methods and which do you recommend?
A There are several techniques for creating tables that exactly or closely mirror a primary table including those that use audit tables, shadow tables, archive tables, and history tables. Basically, in these situations the goal is that for every row inserted into a primary table, a corresponding row is inserted into a secondary audit table. You'll create these types of tables when you want to audit changes to the primary table, keep a permanent record of the original state of the primary table, or create a mirror table that can be used for special calculations or processing, to name a few scenarios.
A There are several techniques for creating tables that exactly or closely mirror a primary table including those that use audit tables, shadow tables, archive tables, and history tables. Basically, in these situations the goal is that for every row inserted into a primary table, a corresponding row is inserted into a secondary audit table. You'll create these types of tables when you want to audit changes to the primary table, keep a permanent record of the original state of the primary table, or create a mirror table that can be used for special calculations or processing, to name a few scenarios.
Since this process entails taking a row of data and copying it in some form to another table, this is a task that belongs in the database tier (if you do this in the application layer, then inserts/updates made through other apps, batch processes, or ad hoc queries will not move rows to the audit table). One way to accomplish this is to modify the stored procedure that inserts into, updates, or deletes from the primary table to also insert or update a record in the secondary table. One consequence of using a single stored procedure to do this is that an explicit transaction should be created to wrap both INSERT statements inside of an atomic transaction. If one of the INSERT statements fails, it could raise an error and roll back the transaction. Another consequence of this solution is that only inserts made through this stored procedure will be made to both the primary and secondary tables. So if any INSERT statements are issued directly against the primary table or even through another stored procedure, they will not be copied to the secondary table. Using this type of solution leaves open the possibility that the primary and secondary tables could get out of sync.
Another way to tackle this situation is to use triggers. AFTER INSERT triggers are commonly used when actions (such as populating shadow tables) must be taken following data modifications on a primary table. For this example, I will mirror all rows inserted into the Region table of the Northwind database to a secondary table called Region_Audit:
|
CREATE TABLE Region_Audit
(
RegionID INT NOT NULL,
RegionDescription NCHAR(50) NULL
)
|
The following trigger will insert a copy of the new region row into the audit/shadow Region_Audit table:
|
CREATE TRIGGER trRegion_Audit_Insert ON [Region] FOR INSERT
AS
INSERT INTO Region_Audit (RegionID, RegionDescription)
SELECT r.RegionID, r.RegionDescription
FROM Region r INNER JOIN inserted i ON i.RegionID = r.RegionID
GO
|
Using a trigger in this type of situation covers the bases more thoroughly than the first technique. The trigger makes sure that no matter how the data is inserted into the primary table, it will also be inserted into the audit table.
Q I have a trigger that must check for an error condition and report it back to whatever application caused the trigger to fire in the first place. How can a trigger raise an error using the RAISERROR statement so that it can be accessed from a Visual Basic-based program? I want the error message that is raised in the trigger to be displayed in the user interface.
Q I have a trigger that must check for an error condition and report it back to whatever application caused the trigger to fire in the first place. How can a trigger raise an error using the RAISERROR statement so that it can be accessed from a Visual Basic-based program? I want the error message that is raised in the trigger to be displayed in the user interface.
A To answer this question, let me set some basic assumptions up front for this scenario. First, let us assume that there is a program using Visual Basic 6.0 that issues an UPDATE statement against the Northwind database table called [Order Details]. The [Order Details] table has an AFTER UPDATE trigger that contains business rules logic that prevents a line item's quantity from being modified to be larger than what is currently in inventory. When an UPDATE statement is issued from the Visual Basic-based application that violates this rule, the trigger will raise an error that needs to bubble up to the application.
|
RAISERROR ('Inventory is out of stock', 16, 1)
|
A To answer this question, let me set some basic assumptions up front for this scenario. First, let us assume that there is a program using Visual Basic 6.0 that issues an UPDATE statement against the Northwind database table called [Order Details]. The [Order Details] table has an AFTER UPDATE trigger that contains business rules logic that prevents a line item's quantity from being modified to be larger than what is currently in inventory. When an UPDATE statement is issued from the Visual Basic-based application that violates this rule, the trigger will raise an error that needs to bubble up to the application.
|
RAISERROR ('Inventory is out of stock', 16, 1)
|
In this case, the T-SQL RAISERROR statement accepts a specific error message, severity, and state. For more details on the syntax and parameters of RAISERROR, refer to the MSDN® Library (
RAISERROR).
When this error is raised inside of the trigger, the calling Visual Basic-based application should have an error handling routine that traps the error. In Visual Basic 6.0, the code that could trap the error and strip the error's description from it could look something like the following:
|
MyErrHandler:
Dim sMsg As String
Dim lErr As Long
Dim sSrc As String
sMsg = Err.Description
lErr = Err.Number
sSrc = Err.Source
|
The error could then be raised again to eventually be thrown back to the user or it could be formatted into a friendlier message that can be presented to the user. The RAISERROR statement in the trigger throws the error back to the Visual Basic-based application which uses standard error-handling code to trap it and then deal with it appropriately. Nothing special needs to be done here to tell the error-handling routine in Visual Basic that this error originated inside of a trigger. In the Microsoft® .NET Framework, error handling is performed slightly differently than in Visual Basic 6.0 since .NET makes the more robust try-catch-finally statement block available. Some sample code to handle an error in C# is shown in Figure 4.
Figure 4 Error Handling in C#
|
try
{
SomeMethodThatCallsMyStoredProc();
}
catch(SqlException sqlEx)
{
lblMyErrorDescLabel.Text = sqlEx.Message;
}
catch(Exception ex)
{
lblMyErrorDescLabel.Text = ex.Message;
}
|
Notice how the multiple catch blocks can catch different types of exceptions. Since code has been written to look for the more specific SqlException, the exception from the trigger would be handled in its catch block. Any other exception type would be caught by the generic Exception catch block.
Send your questions and comments for John to mmdata@microsoft.com.
John Papa is a baseball fanatic who spends most of his summer nights rooting for the Yankees with his two little girls, wife, and faithful dog, Kadi. He has authored several books on ADO, XML, and SQL Server and can often be found speaking at industry conferences such as VSLive.