Using Triggers

Using Triggers

In SQL Server, DML triggers may have the following roles:

• To enforce data integrity, including referential integrity and cascading deletes
  
• To enforce business rules too complex for Default and Check constraints
  
• To log data changes and send notification to administrators via e-mail
  
• To maintain derived information (computed columns, running totals, aggregates, and so on)

Triggers can be implemented to replace all other constraints on a table. A typical example is the use of a trigger to replace the functionality enforced by a foreign key constraint.

It is possible to implement cascading deletes using triggers. For example, if you do not have a foreign key between the Inventory and InventoryProperty tables, you might implement a trigger to monitor the deletion of Inventory records and to delete all associated InventoryProperty records.

Check and Default constraints are limited in that they can base their decision only on the context of current records in the current tables. You can implement a trigger that functions in a manner similar to Check constraints and that verifies the contents of multiple records or even the contents of other tables.

Triggers can be set to create an audit trail of activities performed on a table. For example, you might be interested in obtaining information on who changed the contents of, or specific columns in, the Lease table, and when that user made the changes.

It is possible to create a trigger to notify you when a specific event occurs in the database. For example, in a technical support system, you might send e-mail to the person responsible for dispatching technical staff, to inform that person that a request for technical support has been received. In an inventory system, you might automatically generate a purchase order if the quantity of an inventory item falls below the specified level.

Triggers are suitable for computing and storing calculated columns, running totals, and other aggregates in the database. For example, to speed up reporting, you might decide to keep a total of ordered items in an order table.

DDL triggers are presenting us with completely new area of opportunities. They could be used:

• To establish a traceable record of schema changes to database objects for auditing purposes
  
• To log security changes on a server
  
• To prevent certain types of schema changes
  
• To ensure that a set of database objects is identical across a group of databases or servers

Cascading Deletes

Usually, referential integrity between two tables is implemented with a foreign key, such as in the following illustration:

In such cases, a foreign key prevents the user from deleting records from a parent table (Inventory) if a record is referenced in a linked table (InventoryProperty). The only way to delete the record would be to use the following code:

Delete dbo.InventoryProperty
Where InventoryId = 222

Delete dbo.Inventory
Where InventoryId = 222

In some cases, the system design requirements might call for cascading deletes, which automatically delete records from the linked table when the record in the parent table is deleted. In this case, only one command is required to delete any instance of an asset with an InventoryId value of 222:

Delete dbo.Inventory
Where InventoryId = 222

SQL Server 2000 introduced cascading referential integrity constraints that can implement this behavior. In SQL Server 7.0 and earlier versions, you had to use triggers to implement cascading operations. It is now recommended to use cascading referential integrity constraints, but since that is not possible in some cases (for example, when tables are in different databases), you should know how to do it with triggers.

The following example creates two new tables (without a foreign key), populates them with a few records, and creates a trigger that will implement a cascading delete:

Create Table dbo.MyInventory
     (
     Inventoryid int Not Null Identity (1, 1),
     EqId int Null,
     LocationId int Null,
     StatusId tinyint Null,
     LeaseId int Null,
     LeaseScheduleId int Null,
     OwnerId int Null,
     Rent smallmoney Null,
     Lease smallmoney Null,
     Cost smallmoney Null, 
     AcquisitionTypeID tinyint Null
     )
Go

Alter Table dbo.MyInventory Add Constraint
     PK_Inventory Primary Key Nonclustered
     (
     Inventoryid
     )
Go

Create Table dbo.MyInventoryProperty
     (
     InventoryId int Not Null,
     PropertyId smallint Not Null,
     Value varchar(50) Null
     )
Go
Alter Table dbo.MyInventoryProperty Add Constraint
     PK_InventoryProperty Primary Key Nonclustered
     (
     InventoryId,
     PropertyId
     )
Go
Create Trigger dbo.trMyInventory_CascadingDelete_D
On dbo.MyInventory
After Delete   --For delete
As

If @@Rowcount = 0
     Return
Delete dbo.MyInventoryProperty
where InventoryId In (Select InventoryID from deleted)
Go

Insert into dbo.MyInventory(EqId) Values (1)
Insert into dbo.MyInventory(EqId) Values (2)
Insert into dbo.myInventory(EqId) Values (3)
Insert into dbo.myInventory(EqId) Values (4)
Insert into dbo.myInventory(EqId) Values (5)

Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 1, 'ACME')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 2, 'Turbo')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (1, 3, '311')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 1, 'ACME')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 2, 'TurboPro')
Insert into MyInventoryProperty(InventoryId, PropertyId, Value)
Values (2, 3, '312')
Go

Delete MyInventory
Where InventoryId = 1

Select * from MyInventory
Select * from MyInventoryProperty

Aggregates

Imagine that users of an Asset5 database are often clogging the Inventory table. One operation that they perform frequently is the execution of reports that prepare the sum of all monthly lease payments per lease schedule. If the sum were prepared in advance, the report would be available in an instant, the table would be less clogged, and the user would experience fewer locking and deadlocking problems.

To provide this functionality, you could create one or more triggers to maintain the PeriodicTotalAmount field in the LeaseSchedule table. The field will contain the sum of lease payments for assets in the Inventory table that are associated with a lease schedule.

It is possible to implement diverse solutions for this task. This solution is based on separate triggers for different modification statements.

The Insert trigger is based on a relatively complex Update statement with a subquery based on the contents of the Inserted table. Each new record increments the total in the related lease schedule.

The Coalesce statement is used to replace nulls with zeros in the calculation. The trigger evaluates the number of records affected by the modification statement at the beginning and, if no records are affected, aborts further execution.

This solution executes properly even when multiple records are inserted with one statement:

Create Trigger dbo.trInventory_Lease_I
On dbo.Inventory
after Insert     -- For Insert
As

If @@Rowcount = 0
     return

-- add inserted leases to total amount Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
     LeaseSchedule.PeriodicTotalAmount
     + Coalesce(i.Lease, 0)
from dbo.LeaseSchedule LeaseSchedule
     inner join inserted i
     on LeaseSchedule.ScheduleId = i.LeaseScheduleId
Go

The Delete trigger is very similar to the previous trigger. The main difference is that the values from the Deleted table are subtracted from the total, as shown here:

Create Trigger dbo.trInventory_Lease_D
On dbo.Inventory
After Delete    -- For delete
As

If @@Rowcount = 0
     Return

-- subtract deleted leases from total amount
Update LeaseSchedule
Set LeaseSchedule.PeriodicTotalAmount =
     LeaseSchedule.PeriodicTotalAmount
     - Coalesce(d.Lease, 0)
from dbo.LeaseSchedule LeaseSchedule
   inner join deleted d
   on LeaseSchedule.ScheduleId = d.LeaseScheduleId
Go

The Update trigger is the most complicated. The calculation of a total is performed only if the Lease and LeaseScheduleId fields are referenced by the Update statement. The trigger then subtracts the Lease amounts from the deleted records and adds the Lease amounts from the inserted records to the related totals:

Create Trigger dbo.trInventory_Lease_U
On dbo.Inventory
After Update  -- For Update
As

if @@Rowcount = 0
     return

If Update (Lease) or Update(LeaseScheduleId)
begin

     -- subtract deleted leases from total amount
     Update LeaseSchedule
     Set LeaseSchedule.PeriodicTotalAmount =
          LeaseSchedule.PeriodicTotalAmount
          - Coalesce(d.Lease, 0)
     From dbo.LeaseSchedule LeaseSchedule
        inner join deleted d
        On LeaseSchedule.ScheduleId = d.LeaseScheduleId

     -- add inserted leases to total amount
     Update LeaseSchedule
     Set LeaseSchedule.PeriodicTotalAmount =
           LeaseSchedule.PeriodicTotalAmount
           + Coalesce(i.Lease, 0)
     From dbo.LeaseSchedule LeaseSchedule
     inner join inserted i
     On LeaseSchedule.ScheduleId = i.LeaseScheduleId

End
Go

Enforce Schema Integrity Among Objects on Different Servers or Databases

I showed you earlier how DDL triggers can be used to audit and prevent changes on database objects. They could also be used to maintain a database schema. In the following example, a large table is horizontally partitioned into quarterly tables (to make management easier). Each table should store sales information about sales in one quarter. Therefore, there are four tables that should be maintained identically:

Create database Quarterly
Go
Use Quarterly
Go
Create table Sales1(
  PartId int,
  CustomerId int,
  SalesDate smallint,
  Sales float)
Go
Create table Sales2(
  PartId int,
  CustomerId int,
  SalesDate smallint,
  Sales float)
Go
Create table Sales3(
  PartId int,
  CustomerId int,
  SalesDate smallint,
  Sales float)
Go
Create table Sales4(
  PartId int,
  CustomerId int,
  SalesDate smallint,
  Sales float)
Go

After the set of tables is created, we could create a DDL trigger to capture database changes on the Sales1 table and propagate it to other tables. The trigger is based on the Transact-SQL command captured using EventData():

CREATE TRIGGER trdReplicateSalesTableChanges
ON DATABASE
AFTER DDL_TABLE_EVENTS
AS
Print 'trdReplicateSalesTableChanges started.'
declare @event xml
declare @Object sysname,
      @ObjectType sysname,
      @TSQLCommand nvarchar(max),
      @TSQLCommand2 nvarchar(max),
      @i int

set @event = EVENTDATA()
set @Object = @event.value('(/EVENT_INSTANCE/ObjectName)[1]', 'nvarchar(100)') 1234567890123456789012345678901234567890 123456789012345678901234567890
set @ObjectType = @event.value('(/EVENT_INSTANCE/ObjectType)[1]', 'nvarchar(100)')
set @TSQLCommand = @event.value('(/EVENT_INSTANCE/TSQLCommand)[1]', 'nvarchar(max)')

if (@ObjectType = 'TABLE')
begin
  if (@Object = 'Sales1')
  begin
    -- replace Sales1 string with 2-4
    Set @i = 2
    While @i <= 4
    Begin
      Set @TSQLCommand2 = REPLACE(@TSQLCommand,'Sales1',
       'Sales' + Cast(@i as varchar))
      --execute
      print @TSQLCommand2
      Exec Sp_executeSql @TSQLCommand2
      set @i = @i + 1
    end
  end
end
GO

To test the trigger, we will add a column to the table:

alter table Sales1
   add StoreId int null

The trigger will replicate the change to other tables and print the debug information:

trdReplicateSalesTableChanges started. alter table Sales2
   add StoreId int null
alter table Sales3
   add StoreId int null
alter table Sales4
   add StoreId int null

It is justified to leave the print statement in this trigger, since the trigger is designed to be used only during the administrative tasks on table schemas.

NOTE

---

There may be changes that are too complex and that cannot be propagated correctly this way. You should test results in development and test environment before you use this method in production.