Types of Trigger

There are three main types of triggers that fire on INSERT, DELETE, or UPDATE actions. Like stored procedures, these can also be encrypted for extra security, and more on this will be covered in the next chapter.

FOR

This is the most basic trigger found in virtually all corporations where versions of SQL Server prior to SQL Server 2000 exist. This syntax has been replaced with the AFTER trigger action.

AFTER

This is the default definition while creating a trigger. AFTER triggers will fire after checking the concerned constraint once the deleted and inserted tables have been created, after the data has been modified, but before it has been committed to the database. Whether the underlying data modification is surrounded by a BEGIN TRANSACTION or not, it is not until completion of this trigger that the transaction takes effect.

Note

If we have NTEXT, TEXT, or IMAGE data types within our triggered table, then we will not be able to reference those columns from the inserted and deleted tables. If we need to reference these data types, then it is necessary to use INSTEAD OF triggers.

To demonstrate the AFTER trigger, we will place records into an audit table, which will be populated from triggers on the orders table. Note that northwind is used as the default database in all the code snippets in this chapter. The description of the table is:

     CREATE TABLE Orders_Audit (       OrderID INT ,       CustomerID NCHAR (5) NULL ,       EmployeeID INT NULL ,       OrderDate DATETIME NULL ,       RequiredDate DATETIME NULL ,       ShippedDate DATETIME NULL ,       ShipVia INT NULL ,       Freight MONEY NULL ,       ShipName NVARCHAR (40) NULL ,       ShipAddress NVARCHAR (60) NULL ,       ShipCity NVARCHAR (15) NULL ,       ShipRegion NVARCHAR (15) NULL ,       ShipPostalCode NVARCHAR (10) NULL ,       ShipCountry NVARCHAR (15) NULL ,       DateAdded DATETIME NOT NULL DEFAULT GETDATE() ) 

The new column that was absent in the orders table is a simple DATETIME stamp of when the record was inserted. While creating audit records, it maybe necessary to record under which login the modification was made, depending on how our users connect to the database. We can record which connection made the modification by using SELECT USER. There are ways of getting the UID into a trigger table, like having a user column on the underlying table that is populated using a parameter passed to the calling stored procedure.

The following T-SQL code will create a record of how the modified orders rows looked before any update or deletion took place (only the shipping or required dates are altered):

     CREATE TRIGGER tr_iud_Orders     ON Orders     AFTER INSERT,UPDATE,DELETE     AS     BEGIN     -- If either of these two dates altered, then create the audit record     -- If either are not altered, then no audit record     IF UPDATE(ShippedDate) OR UPDATE(RequiredDate) THEN     INSERT INTO Orders_Audit (OrderID ,CustomerID ,EmployeeID ,OrderDate ,      RequiredDate ,ShippedDate ,ShipVia ,Freight ,ShipName ,ShipAddress ,      ShipCity ,ShipRegion ,ShipPostalCode ,ShipCountry )     SELECT OrderID , CustomerID , EmployeeID , OrderDate , RequiredDate ,         ShippedDate , ShipVia , Freight , ShipName , ShipAddress ,         ShipCity , ShipRegion , ShipPostalCode , ShipCountry       FROM deleted     END 

This is a case where we might wish to have more than a trigger for each action, depending on how we want to use the table; but if there will be a high volume of inserts, compared to updates and deletes, we will slow down our insertion as we will work with the deleted table that will have no rows. The trigger simply places a row in the audit table; therefore, we might not have an INSERT action fire the trigger.

We can make a trigger as complex as we wish for our auditing. If we want to audit INSERT records, then we can modify this trigger so that if @@ROWCOUNT in the deleted table was 0, we can make the trigger insert rows into the audit table from the inserted table. The dark side is that the first modification to the row will generate a duplicate row of the same data. Therefore, if we want to modify every row in the audit table there is no straightforward solution, but we can have an indicator on the audit table to show whether the row is an insertion, modification, or deletion, and ignore any insertion records for the unique key if there are any subsequent rows for the key.

Here, we have a table showing how the audit table and the underlying table will look, for each action:

	Orders	Orders_Audit
INSERT	New row	No row
UPDATE	Updated Row	Previous rows values
DELETE	No row	Previous rows values

Important

AFTER triggers will only fire if all constraints placed on the associated table are valid. When building a trigger it is crucial that the code does not violate any constraint validation placed on a column if it completes any data modification. Otherwise, this will result in an error.

Ordering Trigger Actions

We can have more than one trigger for each action, to make modularisation of functionality and maintenance of our code easier. For example, having a trigger specifically for auditing records, such as creating an image of the record in an audit table, and another to perform any business logic validation is better than having a single trigger to do both.

It is also possible to define when the AFTER trigger should fire. If we have a trigger defined to fire first and wish for a different one instead, we will have to remove the existing first trigger before adding a new one. It is not possible to simply replace the name of the trigger.

Taking the audit and business rules validation scenario one step further, we could create an audit table with the same rules as the main table, and add a column for DATETIME of the row generated, and then another column for an audit message. If we have the audit record insertion as the first trigger, we can add in any business failure message into this record when the second business rules validation trigger fires. However, we can also have the audit trigger fire last so that all eventual modifications to the data are picked up. In addition, if any other triggers cause a rollback, then no audit record will be created, which might be the scenario we wish to have. It all depends to how we want to create audit records, and the better practice will be to have rollbacks in triggers to fire sooner rather than later.

Another scenario could be when a price rise is placed on inventory. A stored procedure can run, which will update the tax value on each stock item. The first trigger can be an audit trail, the second trigger can check if the new cost of the item is above a certain level and apply a new level of discount or shipping charge, and a third can modify a table holding statistics on stock, value, taxation levels, and so on.

We can call sp_settriggerorder system stored procedure to set the order of triggers:

     sp_settriggerorder @triggername, @order, @stmttype 

Important

If there are three or more AFTER triggers, those not named as first or last will fire in any order.

INSTEAD OF

Until SQL Server 2000, it was not possible to have a trigger on a view. Prior to SQL Server 2000, a view was simply to provide a specialized view on a set of data. However, it is now possible to modify the underlying data displayed in a view. Modifying data through a view doesn't need a trigger necessarily, but it's use gives control over the modifications. By defining an INSTEAD OF trigger, we can prevent any modifications through a direct T-SQL statement on a view, and control those modifications through the trigger.

Note

INSTEAD OF triggers differ from AFTER triggers as they run before any constraint checking. Therefore, these can supplement any constraint checking, if required.

Unlike AFTER triggers, we can only define one INSTEAD OF trigger per table or view. If we want to follow the scenarios defined earlier in this section, with multiple triggers, then we can get the trigger to either have all the code in one place, or call other stored procedures if necessary. This also means that we cannot set a trigger default order for INSTEAD OF triggers, as there is only one.

Although INSTEAD OF triggers fire before constraint checks, we should not replace constraints with triggers. More on this will be discussed in the Triggers Versus Constraints section.

If we have cascading actions defined when a foreign key is placed on the underlying tables, then we cannot have a corresponding INSTEAD OF trigger defined. Therefore, if we have cascading DELETEs, we cannot have an INSTEAD OF DELETE trigger.

Cascading

It is possible for one trigger to alter data in another table and the trigger on that table to fire. This is known as cascading triggers. Cascading triggers do not exist on INSTEAD OF triggers, but only on AFTER triggers. Cascading triggers execute in the sequence:

• If there are any cascading actions selected when creating a foreign key on the table with the trigger, then these will fire first

• Then, the trigger associated with the modified table will fire

• Each table modified in order will then fire any trigger associated with it

Cascading triggers have obvious performance hits on our application. We can use cascading triggers only if a trigger is performing either some audit action or an update, such as summing a set of orders for a product into a sales audit table. If the potential cascading triggers perform only validation, then cascading should not be allowed.

To allow nested triggers, we need to configure the database. This can be done in T-SQL as:

     sp_configure "nested triggers",1 

No Rows Updated

A trigger will fire on the triggering action even if no rows have been affected. Take a look at the following update trigger. This will fire on an UPDATE action on the orders table.

     CREATE TRIGGER tr_upd_OrdersNoRows     ON Orders     AFTER UPDATE     AS     BEGIN         RAISERROR('Update occurred',1,1)     END 

If no rows in the orders table are actually updated, the RAISERROR statement will run.

     UPDATE Orders     SET RequiredDate = Null     WHERE CustomerId = 'NOROW' 

Once the above code is executed, we will see the following output. It looks as if a row has been updated but the (0 row(s) affected) line says that there have been none.

 Msg 50000, Level 1, State 50000 Update occurred (0 row(s) affected) 

The correct method of writing a trigger is to use @@ROWCOUNT to check how many rows were affected. We can do this as the first statement in the trigger, or by checking the deleted and inserted tables.

Important

If we want to check the triggered table, then we must put @@ROWCOUNT first in the trigger.

Let's rewrite the above trigger accordingly to fire the trigger if no update occurs:

     ALTER TRIGGER tr_upd_OrdersNoRows     ON Orders     AFTER UPDATE     AS     BEGIN       IF @@ROWCOUNT = 0         RETURN       RAISERROR('Update occurred',1,1)     END