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.