SQL Server Triggers

In previous post we talked about views, today we will review triggers and what purpose they serve. Triggers are useful but they should be used judiciously.

A trigger is a T-SQL script or a special stored procedure that is bound to the table and automatically executes based on a certain action on the table. For example, you can have a trigger that executes when a record is inserted, updated or deleted in a table.
There are two types of triggers - Data Definition Language (DDL) triggers and Data Manipulation Language (DML) triggers. DML triggers execute in response to data modification, for example, when you insert/update/delete a record. DDL triggers are executed when you modify data definition for example - adding a column to the table etc.

DML Triggers
DML Triggers can be further categorized into AFTER Trigger and INSTEAD OF Trigger. After triggers are executed after the statement with which they are associated with has been executed.
For example, suppose you have an audit table and anytime a record is inserted in the main table, you want to copy the newly inserted record in an audit table.

CREATE TRIGGER trgAfterInsert ON [dbo].[Customers] 
FOR INSERT
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =newRow.CustomerID from inserted newRow;    
    SELECT @FirstName = newRow.FirstName from inserted newRow;    
    SELECT @LastName = newRow.LastName from inserted newRow;    
    SELECT @EmailAddress = newRow.EmailAddress from inserted newRow;    
    
    INSERT INTO Customers_Audit
           (CustomerID,FirstName,LastName,EmailAddress) 
    values(@CustomerID,@FirstName,@LastName,@EmailAddress);

    PRINT 'AFTER INSERT trigger fired.'
GO

When you insert a record in customers table, it will fire this AFTER trigger and insert the newly inserted record into Customers_Audit table.

Similarly, you can create a trigger for UPDATE or DELETE.

Notice "from inserted" is a logical table inserted method that you can use to get the values from newly inserted rows. There is no logical update method, but you can use the same inserted method to get the updated rows and do something with them. For deleted rows, there is logical deleted method to get the deleted values. For example, I can create a new trigger when a record is deleted.

CREATE TRIGGER trgAfterDelete ON [dbo].[Customers] 
FOR DELETE
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =deletedRow.CustomerID from deleted deletedRow;    
    SELECT @FirstName = deletedRow.FirstName from deleted deletedRow;    
    SELECT @LastName = deletedRow.LastName from deleted deletedRow;    
    SELECT @EmailAddress = deletedRow.EmailAddress from deleted deletedRow;    
    
    INSERT INTO Customers_Audit
           (CustomerID,FirstName,LastName,EmailAddress) 
    values(@CustomerID,@FirstName,@LastName,@EmailAddress);

    PRINT 'AFTER DELETED trigger fired.'
GO

INSTEAD OF Trigger
INSTEAD OF Trigger allows you to intercept an execution and then roll back or commit transaction based on your criteria. For example, you don't want to delete certain records. You can create an INSTEAD OF trigger that can intercept the deletion, detect if the condition is satisfied and then either commit the deletion or rollback.

For example, I don't want to delete any customer whose last name is "Smith". I can create an INSTEAD OF trigger to prevent deleting such customers.

CREATE TRIGGER trgInsteadOfDelete ON [dbo].[Customers] 
INSTEAD OF DELETE
AS
    Declare @CustomerID INT;
    Declare @FirstName varchar(50);
    Declare @LastName varchar(50);
    Declare @EmailAddress varchar(100);
    
    SELECT @CustomerID =deletedRow.CustomerID from deleted deletedRow;    
    SELECT @FirstName = deletedRow.FirstName from deleted deletedRow;    
    SELECT @LastName = deletedRow.LastName from deleted deletedRow;    
    SELECT @EmailAddress = deletedRow.EmailAddress from deleted deletedRow;    
    
    IF (@LastName='Smith')
        BEGIN
            RAISERROR('Cannot Delete',16,1)
            ROLLBACK;
        END
    ELSE
        BEGIN
            DELETE FROM Customers WHERE CustomerID=@CustomerID
            COMMIT
        END
        
GO

DDL Trigger
DDL Triggers are fired when a table schema is modified. If I want to prevent any table modification, I can create a trigger like this...

CREATE TRIGGER TrgAlterTable
ON DATABASE 
FOR DROP_TABLE, ALTER_TABLE 
AS 
PRINT 'You must disable Trigger "TrgAlterTable" to drop or alter tables!' 
ROLLBACK ;

Unless I disable or drop this trigger, I will not be able to drop or modify a table.

Enabling / Disabling a Trigger
You can disable all triggers or a specific trigger by executing the following script.

ALTER TABLE Customers {ENABLE|DISBALE} TRIGGER ALL 

To enable or disable a specific trigger, simply replace "ALL" with the trigger name.

While triggers serve a useful purpose, one of the issue I have with them is that they are hidden and after you have long forgotten, troubleshooting an issue becomes a problem because you just don't think about the triggers. That's why it is important to have some kind of messaging/logging in triggers to trace an issue.

Thank you.

Using SQL Views to Insert/Update/Delete Data

In previous post we discussed SQL Server Views and how you can use them to logically abstract some of the complexities of the underlying schema. We focused mainly on selecting data using views.

Views also allow you to insert/update/delete data, but they are limited in their power when it comes to modifying data.

Think back for a second - when you insert data in a table, you can only insert in one table at a time. If more than one table is related via referential integrity, you must insert in one table and then insert in subsequent tables using the referential integrity key from the primary table. Similarly, when you are updating or deleting data from the tables, you can only update/delete from one table at a time. Additionally, the user must have proper permissions to do.

Same restrictions apply when you are using views to achieve this. Some of the restrictions that apply to views are...

• Insert/Update/Delete statements must reference columns from only one base table.
• Columns in the view must relate to underlying columns in the base table directly i.e. they cannot be computed columns such as AVG, COUNT, SUM, MIN, MAX etc. 
• The columns being modified cannot also be grouped or affected by DISTINCT, GROUP BY or HAVING clauses
• If you think about it, it makes sense. SQL Server wouldn't know which column to modify if you have applied these clauses.
• If you have used WITH CHECK OPTION (see previous post about this), you can't use TOP in SELECT statement of the view.
• If you have any sub/nested queries, same restrictions apply to them as well.
• Constraints defined at the table columns such as not null, referential integrityetc. also apply when modifying via views.

In previous post we created a view to retrieve some records from multiple tables. Let's see if we can use the same view to update or delete a record.

CREATE VIEW view_Orders

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
Count(O.OrderID)As TotalOrders,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers 
C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
Group By C.FirstName,C.LastName,S.FirstName,S.LastName
GO

Since both customer's and sales person's first and last name are used in Group By clause and also the OrderID column is using Count function (OrderID is identity column so you can't update anyway, but just wanted to make a point), none of the columns in this view can be updated or deleted.

What if I create a view like this...

CREATE VIEW view_OrdersList

AS
SELECT C.FirstName As CustomerFirstName,C.LastName As CustomerLastName,
O.OrderID As OrderNumber,S.FirstName As SalesFirstName,
S.LastName As SalesLastName FROM Customers 
C INNER JOIN Orders O ON C.CustomerID=O.CustomerID
INNER JOIN SalesPerson S ON O.SalesPersonID=S.SalesPersonID
GO

I should be able to use this view to pretty much update any column i.e. Customer First Name, Last Name and Sales Person First and Last Name. I can't update Order ID because it is identity column.

In my database, this view returns the following records

I can run the following update commands one after another to update the customer last name and sales person last name using the same view, even though they are in two different underlying tables.

UPDATE View_OrdersList SET CustomerLastName='Roberts' WHERE 
CustomerLastName='Smith'
GO
UPDATE View_OrdersList SET SalesLastName='Kimberly' WHERE 
SalesLastName='Kimber'
GO

But what about updating both records using the same update query?

UPDATE View_OrdersList SET CustomerLastName='Roberts',SalesLastName='Kimberly' 
WHERE CustomerLastName='Smith' AND SalesLastName='Kimber'
GO

This will result in the following error

Needless to say it violates the above defined rules i.e. only one table can be updated at a time, hence the error.

I can also insert using the same view or delete a record as long as no referential integrity or other constraints are violated.

A view abstracts away some of the complexities and allows you to use the same view to select/update/insert/delete, although you still have to ensure all the conditions are satisfied just as you would when making modifications directly in tables.

Thank you.