Thursday, May 12, 2016

Basic Insert Trigger Example


Below is the trigger which triggers every time a record insert in to the table to update below mentioned columns.


CREATE TRIGGER Trg_ServiceTickets ON DBO.ServiceTickets
FOR INSERT
AS
    DECLARE  @ServiceAddress         VARCHAR(481)
             ,@ServiceAddressCity     VARCHAR(60)
             ,@ServiceAddressState    VARCHAR(60)
             ,@ServiceAddressCountry  VARCHAR(60)
             ,@ServiceTicketID        INT
             ,@Requester              VARCHAR(100)
             ,@TriggerAction          VARCHAR(500)

       SET @ServiceTicketID       = (SELECT  I.ServiceTicketID FROM inserted i)
       SET @ServiceAddress        = (SELECT  I.ServiceAddress FROM inserted i) 
       SET @ServiceAddressCity    = (SELECT  I.ServiceAddressCity FROM inserted i) 
       SET @ServiceAddressState   = (SELECT  I.ServiceAddressState FROM inserted i) 
       SET @ServiceAddressCountry = (SELECT  I.ServiceAddressCountry FROM inserted i) 

       SET @Requester             = (SELECT  I.Requestor FROM inserted i) 
      
       SET @TriggerAction='Insert Trigger to update the ServiceAddress, City, State, Country if they are NULL to '' ';

       UPDATE ServiceTickets
       SET    ServiceAddress          = ISNULL(@ServiceAddress,'')
             ,ServiceAddressCity      = ISNULL(@ServiceAddressCity,'')
             ,ServiceAddressState     = ISNULL(@ServiceAddressState,'')
             ,ServiceAddressCountry   = ISNULL(@ServiceAddressCountry,'')
              ,LastModifiedBy          = CASE WHEN @ServiceAddress IS NULL OR @ServiceAddressCity IS NULL OR @ServiceAddressState IS NULL OR @ServiceAddressCountry IS NULL THEN 'Updated By Trigger : Trg_ServiceTickets'
     ELSE @Requester
     END
     WHERE ServiceTicketTypeID    = 4
     AND  ServiceTicketID = @ServiceTicketID       

PRINT 'AFTER INSERT trigger fired.'
GO


It will also update the lastmodifiedby column with the information as the record is updated by the trigger.

No comments:

Post a Comment