Recently, I wrote a couple of posts around some of the best practices that should be followed when working with Connection Strings. In case you missed them earlier, they are linked below for your kind reference:
In these posts, I mentioned that the application name parameter (and connection strings in general) can be used to alter the behavior of an application. One of the follow-up questions I received was around this topic, and today, I would briefly demonstrate a practical use of this functionality.
How can you modify the application behavior by using the application name parameter of a connection string?
A common requirement that I have encountered in many home-grown enterprise systems which share the same database is to exhibit different auditing behavior based on which application or interface is inserting/updating a piece of data. Most legacy applications implement auditing via database triggers, and checking for the application name in these triggers is the solution to realizing this requirement. Today, I will explore with you how the APP_NAME() function can be used to alter the behavior of an application based on the application name set by the connection string.
Using APP_NAME()
Consider the following trigger that is executed whenever an Employee record is updated. The only purpose of this is to audit the changes made to the Employee record by applications other than the “MyEnterpriseProduct”. These updates could be through interfaces or through any other applications that the organization may be using.
This demo will walk you through using the APP_NAME() function, which returns the application name for the current session if set by the application, within a DDL trigger.
The following code creates an auditing table followed by an update trigger on the Employee table.
USE AdventureWorks2008R2
GO
–Step 01: Prepare the environment
— : Create the auditing table
— : Create the update trigger
IF OBJECT_ID(‘HumanResources.uEmployee’,’TR’) IS NOT NULL
BEGIN
DROP TRIGGER [HumanResources].[uEmployee];
END
GO
IF OBJECT_ID(‘HumanResources.Audit’) IS NOT NULL
BEGIN
DROP TABLE [HumanResources].[Audit];
END
GO
CREATE TABLE [HumanResources].[Audit] (AuditDate DATETIME,
BeforeJobTitleValue VARCHAR(100),
AfterJobTitleValue VARCHAR(100),
ApplicationName VARCHAR(100)
);
GO
CREATE TRIGGER [HumanResources].[uEmployee]
ON [HumanResources].[Employee]
FOR UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @appName VARCHAR(100);
SET @appName = APP_NAME();
IF (@appName != ‘MyEnterpriseProduct’)
BEGIN
INSERT INTO [HumanResources].[Audit]
(AuditDate, BeforeJobTitleValue, AfterJobTitleValue, ApplicationName)
SELECT GETUTCDATE() AS AuditDate,
deleted.JobTitle AS BeforeJobTitleValue,
inserted.JobTitle AS AfterJobTitleValue,
@appName AS ApplicationName
FROM [HumanResources].[Employee] AS Employee
INNER JOIN deleted ON deleted.BusinessEntityID = Employee.BusinessEntityID
INNER JOIN inserted ON deleted.BusinessEntityID = inserted.BusinessEntityID
WHERE Employee.BusinessEntityID = inserted.BusinessEntityID;
END
END
GO
Now, using the methods described in the post “How to test your Connection String using SSMS?”, establish two connections from the SSMS to the test AdventureWorks database – one using the Application Name – “MyEnterpriseProduct” and other using “OtherApplication”. Execute the queries shown below using the respective connections:
–Step 02: Using a connection with Application Name = “MyEnterpriseProduct”,
— update the Employee record
— Confirm that no auditing has been performed
USE AdventureWorks2008R2
GO
–Chief Executive Officer
UPDATE HumanResources.Employee SET JobTitle = ‘CEO’ WHERE BusinessEntityID = 1;
GO
SELECT NULL AS AuditDate,
NULL AS BeforeJobTitleValue,
NULL AS AfterJobTitleValue,
APP_NAME() AS ApplicationName
UNION
SELECT AuditDate,
BeforeJobTitleValue,
AfterJobTitleValue,
ApplicationName
FROM [HumanResources].[Audit];
GO
–Step 03: Using a connection with Application Name = “OtherApplication”,
— update the Employee record
— Confirm that auditing has been performed
USE AdventureWorks2008R2
GO
–Chief Executive Officer
UPDATE HumanResources.Employee SET JobTitle = ‘Chief Executive Officer’ WHERE BusinessEntityID = 1;
GO
SELECT NULL AS AuditDate,
NULL AS BeforeJobTitleValue,
NULL AS AfterJobTitleValue,
APP_NAME() AS ApplicationName
UNION
SELECT AuditDate,
BeforeJobTitleValue,
AfterJobTitleValue,
ApplicationName
FROM [HumanResources].[Audit];
GO
Here’s what one would see in the two different connections:
Query Window |
Audit Date |
BeforeJobTitleValue |
AfterJobTitleValue |
Application Name |
1 |
NULL |
NULL |
NULL |
MyEnterpriseProduct |
2 |
NULL |
NULL |
NULL |
OtherApplication |
2 |
11/16/2012 |
CEO |
Chief Executive Officer |
OtherApplication |
Notice that in the second result set (in the connection with Application Name set to “OtherApplication”, the BeforeJobValue is set to “CEO”, indicating that the first query did actually update the data, but the activity was not logged.
Finally, it’s cleanup time!
–Step 04: Cleanup!
USE AdventureWorks2008R2
GO
IF OBJECT_ID(‘HumanResources.uEmployee’,’TR’) IS NOT NULL
BEGIN
DROP TRIGGER [HumanResources].[uEmployee];
END
GO
IF OBJECT_ID(‘HumanResources.Audit’) IS NOT NULL
BEGIN
DROP TABLE [HumanResources].[Audit];
END
GO
Conclusion
As can be seen in the demonstration above, we were able to change the behavior of the auditing mechanism built into the application based on the parameter (Application Name) supplied within the connection string. Connection strings are therefore very powerful, and I urge all readers to go back, explore and share the results of your experiments through your comments on this post.
Disclaimer: The above example is only intended to demonstrate the use of the system function APP_NAME() and should not be used as a guidance for designing an auditing solution. An auditing solution requires capturing many more parameters and is outside the scope of this discussion.
Until we meet next time,
Be courteous. Drive responsibly.
SQLTwins by Nakul Vachhrajani 