The following two examples show you what can be achieved with triggers and what they should be used for. In the first example users log on to a Web site and complete a form to register for a logon account. A process in the Web service creates a user logon ID and password and sets up credentials in the CRMCustomer table. The logon ID is given a GUID, the 32-bit globally unique identifier. The trigger on the CRMCustomer table, after the insert, captures the GUID on the calling stored procedure and then sets up rows under the same GUID, as the foreign key, in a collection of related tables, such as tables for telephone numbers and e-mail addresses of the same customer.
set ANSI_NULLS ON set QUOTED_IDENTIFIER ON go ALTER TRIGGER [CRMCustomerInsertCascadeTrigger] ON [dbo]. [CRMCustomer] AFTER INSERT AS BEGIN DECLARE @CustomerType int DECLARE @CallingProc varchar (128) DECLARE @Source uniqueidentifier SELECT @CallingProc=OBJECT_NAME(SUBSTRING ( p.context_info, 1, 4)), @Source=CAST(SUBSTRING ( p.context_info, 5, 128) AS uniqueidentifier) FROM master.sysprocesses as p WHERE p.spid=@@SPID PRINT @CallingProc INSERT INTO CRMPhones (CRMCustomerID) SELECT CRMCustomerID FROM inserted INSERT INTO CRMAddresses (CRMCustomerID) SELECT CRMCustomerID FROM inserted INSERT INTO CRMFaxes (CRMCustomerID) SELECT CRMCustomerID FROM inserted INSERT INTO CRMEmails (CRMCustomerID) SELECT CRMCustomerID FROM inserted INSERT INTO AccessPermissions (UserGUID) SELECT CRMCustomerID FROM inserted INSERT INTO SystemUsers (UserGUID, LoginID) SELECT CRMCustomerID, Cast (CRMCustomerID as NVarChar (50)) FROM inserted SET @CustomerType=(SELECT CRMCustomerType FROM inserted) If @CustomerType=1 Begin INSERT INTO CRMProducerData (CRMCustomerID) SELECT CRMCustomerID FROM inserted End INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'CRM','Insert', 'NA', 'CRMCustomer', CRMCustomerID, @Source FROM inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'CRM','Insert', 'NA', 'CRMPhones', CRMCustomerID, @Source FROM inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'CRM','Insert', 'NA', 'CRMAddresses', CRMCustomerID, @Source FROM inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'CRM', 'Insert', 'NA', 'CRMFaxes', CRMCustomerID, @Source FROM inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'CRM', 'Insert', 'NA', 'CRMEmails', CRMCustomerID, @Source FROM Inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'New Login', 'Insert', 'NA', 'SystemUsers', CRMCustomerID, @Source FROM Inserted INSERT INTO AuditTrail (Context, TransactionType, FieldChanged, TableAffected, NewValue, UserGUID) SELECT 'New Login', 'Insert', 'NA', 'AccessPermissions', CRMCustomerID, @Source FROM inserted END
This second trigger is a simple mechanism on a voice mail system that enforces a business rule on the process of deleting messages.
CREATE TRIGGER VoiceMessageDelete ON Messages FOR DELETE AS IF EXISTS (DELETE FROM Messages m INNER JOIN conference c ON m.message_no=c. message_no) BEGIN RAISERROR ('Message cannot be deleted until heard by all conference members', 10, 1) ROLLBACK TRANSACTION END