SQL Server Trigger on View Example

Problem

SQL Server triggers can be useful, but also tricky to use. Most people think about putting triggers on tables, but did you know that you could also create triggers on views? In this tip we will cover how this can be done as well as how to control what can be updated using a view and how to control with a trigger.

Solution

When you are learning about triggers, you will find there are not many topics that are harder to understand. The topic in question is the one we are referring to in this tip, implementing triggers on views. The difficulty arises because you need to have a solid base of knowledge on both triggers and views.

If you have read my previous introductory tip SQL Server Trigger Example, you know that a trigger is a piece of procedural code, like a stored procedure which is only executed when a specific event happens and the trigger its executed. Since we are talking about triggers on views, the triggering event will be any DML statement using the view.

SQL Server Triggers on Views

There are two kinds of DML triggers the FOR (or AFTER) trigger and the INSTEAD OF trigger, but the only one you can use with views are INSTEAD OF triggers. In contrary to the AFTER type which fires after an event, the INSTEAD OF trigger executes instead of the firing statement. In other words, this type of triggers replaces the firing statement.

Due to the nature of INSTEAD OF triggers, which allows us to create a wrapper to DML statements, there are different scenarios in which you may want to use triggers on views in your database application.

Security Enforcement

By using INSTEAD OF triggers, we can change the behavior of INSERT, UPDATE and DELETE statements for a given view. In other words, we can perform certain validations before making a change to the data.

With the combination of a view and a trigger you can create row and cell level security on tables. Before SQL Server 2016 this was the only available method to enforce row level security.

Perform Changes on a Complex View

Views are very restrictive when you want to modify their data, especially if there is more than one table involved in the view.

When you create a view, you must obey the following conditions in order to make the view updateable:

Any DML statement, must reference columns from only one base table.
The columns being modified in the view have to directly reference the underlying table columns. As a consequence, the view’s columns being modified cannot be the result of an aggregate function or a computed column.
Additionally, the columns being modified cannot be affected by GROUP BY, HAVING, or DISTINCT clauses.
The view’s code must not use the TOP clause together with the WITH CHECK OPTION clause.

The only way to make data changes on a non-updateable view is by using INSTEAD OF triggers. This way you can use procedural code to overcome the limitation.

Cross Database Referential Integrity

For those of you who don’t know, there is a limitation on how SQL Server handles referential integrity which makes it unavailable on objects that are on different databases. In order to overcome this limitation, we can use triggers to implement foreign keys on objects amongst databases. Basically, the trigger will perform some validation before changing or deleting any data on the referenced table allowing us to code our own algorithm to check for referential integrity violations. You will find an example on how to implement cross database referential integrity in the tip SQL Server Referential Integrity Across Databases Using Triggers.

Trigger on View Example

I created a few scripts to show you how to implement a very simple security enforcement solution by using views and triggers.

In a test database, run the following script to create a table named Employees.

CREATE TABLE Employees
    (
      EmployeeID int NOT NULL IDENTITY(1, 1) ,
      EmployeeName VARCHAR(50) ,
      EmployeeAddress VARCHAR(50) ,
      MonthSalary NUMERIC(10, 2) ,
      UserID int
      PRIMARY KEY CLUSTERED (EmployeeID)
    )
GO

After creating this table, we need to insert some test data.

SET IDENTITY_INSERT [dbo].[Employees] ON 
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (1, N'Garrison Haney', N'381-4291 Enim, Av.', CAST(6357.00 AS Numeric(10, 2)), 1)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (2, N'Ursa Dyer', N'Ap #596-1792 Odio. St.', CAST(6168.00 AS Numeric(10, 2)), 2)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (4, N'Jack Hensley', N'363-7192 Eu, Av.', CAST(7333.00 AS Numeric(10, 2)), 4)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (5, N'Harriet Williamson', N'369-7806 Vulputate St.', CAST(5574.00 AS Numeric(10, 2)), 5)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (6, N'Rahim Moran', N'675-1652 Venenatis Ave', CAST(9970.00 AS Numeric(10, 2)), 6)
GO
INSERT [dbo].[Employees] ([EmployeeID], [EmployeeName], [EmployeeAddress], [MonthSalary], [UserID]) VALUES (7, N'Kirestin Ingram', N'Ap #255-8512 Adipiscing. St.', CAST(8603.00 AS Numeric(10, 2)), 7)
GO
SET IDENTITY_INSERT [dbo].[Employees] OFF
GO

Now run the next script to create an additional table named Users. This table has a check constraint for the column UserGroup in order to limit its possible values to 'HR', 'IT' and ‘Other’ which aims to represent the security hierarchy of a sample company.

CREATE TABLE [dbo].[Users]
    (
      [UserID] [INT] NOT NULL IDENTITY(1,1) ,
      [UserName] [VARCHAR](50) NOT NULL ,
      [UserGroup] [VARCHAR](50) CHECK ( UserGroup IN ( 'HR', 'IT', 'Other' ) )
                                NOT NULL PRIMARY KEY CLUSTERED ( [UserID] )
    )
ON [PRIMARY]
GO

After creating this table, run the next script (or adapt it to your environment) that will add two existing logins into the Users table. One of the users will have permissions to access salary information when querying the vw_Employees view and the other will not.

INSERT INTO dbo.Users ( UserName, UserGroup )
VALUES  ( 'ORIOM\Daniel', 'HR' )
 
INSERT INTO dbo.Users ( UserName, UserGroup )
VALUES  ( 'ORIOM\Moxi', 'Other' )
GO

Additionally, run the next script to create the vw_Employees view.

CREATE VIEW vw_Employees
AS
    SELECT  EmployeeID ,
            EmployeeName ,
            EmployeeAddress ,
            CASE ( SELECT   UserGroup
                   FROM     dbo.Users
                   WHERE    UserName = SUSER_NAME()
                 )
              WHEN 'HR' THEN MonthSalary
            END AS MonthSalary
    FROM    dbo.Employees

Now that we have created the view it’s time to create the trigger that will protect the vw_Employees from unauthorized changes. The below will just check to see if the person is in the HR group and if not, it will throw an error. An actual update is not done, but could be added to the code below to update the data for the person that has access.

CREATE TRIGGER TR_vw_Employees ON vw_Employees
INSTEAD OF UPDATE
AS
IF UPDATE(MonthSalary)
BEGIN
   IF NOT EXISTS (SELECT 0
                  FROM   dbo.Users
                  WHERE  UserName = SUSER_NAME() AND UserGroup = 'HR')
   BEGIN
      ;THROW  51000, 'You must be in the HR group to update salary', 1;  
   END    
END
GO

Finally, we have everything set up to start our test scenario.

In the next script you will see that I execute the same update statement in the context of the two different logins we had granted permission on the Users table by using the EXECUTE AS command.

PRINT 'Executed as ORIOM\Daniel'
BEGIN TRANSACTION 
UPDATE dbo.vw_Employees SET MonthSalary = 999999
ROLLBACK
 
PRINT '---------------------------------'
 
PRINT 'Executed as ORIOM\Moxi'
EXECUTE AS LOGIN = 'ORIOM\Moxi';
BEGIN TRANSACTION 
UPDATE dbo.vw_Employees SET MonthSalary = 999999
ROLLBACK
REVERT

You can see in the screen capture below we have the expected results. The update statement was successful when run in the context of login ORIOM\Daniel, who is member of the "HR" group and failed when executed in the context of login ORIOM\Moxi because this login is assigned to the "Other" group instead of the "HR" group.

Results of executing the update of the view in the context of two different logins.

Take into consideration that this is an example intended to show how to use triggers on views and therefore I omitted a few steps that in case you want to implement something like this in production you have to do some additional steps. Just to know, you will need to secure the employees table so those who access the database cannot modify the data in the table directly.

Next Steps

If you found it hard to understand the way I captured which DML operation was performed in the Employees table take a look at this tip: Understanding SQL Server inserted and deleted tables for DML triggers.
For another example of an INSTEAD OF trigger you can check out this tip: Using INSTEAD OF triggers in SQL Server for DML operations.
You can read more about the EXECUTE AS command in the next tip: Granting permission with the EXECUTE AS command in SQL Server.
Additionally you will need to read this other tip that explains how to use the REVERT clause: Switching Stored Procedure Execution Context in SQL Server using the REVERT clause.
If you are interested on how to implement row or cell level security with triggers and views you can read this previous tip of mine: Implementing SQL Server Row and Cell Level Security.
In case you need to use triggers to enforce referential integrity across databases I suggest that you read the following tip of mine: SQL Server Referential Integrity Across Databases Using Triggers.
Stay tuned to SQL Server Triggers Tips Category for more tips and tricks.

Resources

Community

Subscribe