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
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
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
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.