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 noti.cation 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,      PropertId 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 = (Sevent .value (' (/EVENT_INSTANCE/ObjectName) [1] ', 'nvarchar(100)') 1234567890123456789012345678901234567890123456789012345678901234567890 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 ((Si as varchar) )       --execute      print @TSQLCommand2      Exec Sp_executeSql @TSQLCommand2         set (Si = (Si + 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 he propagated correctly this way. You should test results in development and test environment before you use this method in production.

You can apply similar triggers in a scenario when data is split among databases or even servers.