Putting Triggers to Good Use


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




Microsoft SQL Server 2005. The Complete Reference
Microsoft SQL Server 2005: The Complete Reference: Full Coverage of all New and Improved Features
ISBN: 0072261528
EAN: 2147483647
Year: 2006
Pages: 239

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net