AFTER INSERT TRIGGER Example Using the Inserted and Deleted Tables

DML trigger statements use two special tables: the deleted table and the inserted tables. SQL Server automatically creates and manages these tables. You can use these temporary, memory-resident tables to test the effects of certain data modifications and to set conditions for DML trigger actions. You cannot directly modify the data in the tables or perform data definition language (DDL) operations on the tables, such as CREATE INDEX.

-- ================================================
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:        Jayendrasinh Gohil

-- Create date: 10/14/2010

-- Description:   This Trigger is use when after data is instered in MoterFuelInventory table. If the data is null then

                  Set it to 0.00 .

-- =============================================
ALTER  TRIGGER t_MoterFuelInventoryBalInsert
   ON  dbo.MotorFuelInventory
   AFTER INSERT
AS
BEGIN
      -- SET NOCOUNT ON added to prevent extra result sets from
      -- interfering with SELECT statements.
      SET NOCOUNT ON;

    -- Insert statements for trigger here  

       DECLARE @MotorFuelInventoryId INT       

    SET @MotorFuelInventoryId = 0

    SELECT @MotorFuelInventoryId = I.Id        
      FROM INSERTED I

      UPDATE [Storeaccounting].[dbo].[MotorFuelInventory]
   SET [StoreEntryId] = ISNULL([StoreEntryId],0.00)
      ,[BillOfLading] = ISNULL([BillOfLading],0.00)
      ,[BillOfLading1] = ISNULL([BillOfLading1],0.00)
      ,[BillOfLading2] = ISNULL([BillOfLading2],0.00)
      ,[BalForwardSuper] = ISNULL([BalForwardSuper],0.00)
      ,[LoadReceivedSuper] = ISNULL([LoadReceivedSuper],0.00)
      ,[LoadReceivedSuper1] = ISNULL([LoadReceivedSuper1],0.00)
      ,[LoadReceivedSuper2] = ISNULL([LoadReceivedSuper2],0.00)
      ,[TotalLoadReceivedSuper] = ISNULL([TotalLoadReceivedSuper],0.00)
      ,[GallonsSoldSuper] = ISNULL([GallonsSoldSuper],0.00)
      ,[BookInventorySuper] = ISNULL([BookInventorySuper],0.00)
      ,[OverShortSuper] = ISNULL([OverShortSuper],0.00)
      ,[ActStickReadingSuper] = ISNULL([ActStickReadingSuper],0.00)
      ,[WaterLevelSuper] = ISNULL([WaterLevelSuper],0.00)
      ,[BalForwardPlus] = ISNULL([BalForwardPlus],0.00)
      ,[LoadReceivedPlus] = ISNULL([LoadReceivedPlus],0.00)
      ,[LoadReceivedPlus1] = ISNULL([LoadReceivedPlus1],0.00)
      ,[LoadReceivedPlus2] = ISNULL([LoadReceivedPlus2],0.00)
      ,[TotalLoadReceivedPlus] = ISNULL([TotalLoadReceivedPlus],0.00)
      ,[GallonsSoldPlus] = ISNULL([GallonsSoldPlus],0.00)
      ,[BookInventoryPlus] = ISNULL([BookInventoryPlus],0.00)
      ,[OverShortPlus] = ISNULL([OverShortPlus],0.00)
      ,[ActStickReadingPlus] = ISNULL([ActStickReadingPlus],0.00)
      ,[WaterLevelPlus] = ISNULL([WaterLevelPlus],0.00)
      ,[BalForwardRegular] = ISNULL([BalForwardRegular],0.00)
      ,[LoadReceivedRegular] = ISNULL([LoadReceivedRegular],0.00)
      ,[LoadReceivedRegular1] = ISNULL([LoadReceivedRegular1],0.00)
      ,[LoadReceivedRegular2] = ISNULL([LoadReceivedRegular2],0.00)
      ,[TotalLoadReceivedRegular] = ISNULL([TotalLoadReceivedRegular],0.00)
      ,[GallonsSoldRegular] = ISNULL([GallonsSoldRegular],0.00)
      ,[BookInventoryRegular] = ISNULL([BookInventoryRegular],0.00)
      ,[OverShortRegular] = ISNULL([OverShortRegular],0.00)
      ,[ActStickReadingRegular] = ISNULL([ActStickReadingRegular],0.00)
      ,[WaterLevelRegular] = ISNULL([WaterLevelRegular],0.00)
      ,[BalForwardDiesel] = ISNULL([BalForwardDiesel],0.00)
      ,[LoadReceivedDiesel] = ISNULL([LoadReceivedDiesel],0.00)
      ,[LoadReceivedDiesel1] = ISNULL([LoadReceivedDiesel1],0.00)
      ,[LoadReceivedDiesel2] = ISNULL([LoadReceivedDiesel2],0.00)
      ,[TotalLoadReceivedDiesel] = ISNULL([TotalLoadReceivedDiesel],0.00)
      ,[GallonsSoldDiesel] = ISNULL([GallonsSoldDiesel],0.00)
      ,[BookInventoryDiesel] = ISNULL([BookInventoryDiesel],0.00)
      ,[OverShortDiesel] = ISNULL([OverShortDiesel],0.00)
      ,[ActStickReadingDiesel] = ISNULL([ActStickReadingDiesel],0.00)
      ,[WaterLevelDiesel] = ISNULL([WaterLevelDiesel],0.00)
      ,[BalForwardRF] = ISNULL([BalForwardRF],0.00)
      ,[LoadReceivedRF] = ISNULL([LoadReceivedRF],0.00)
      ,[LoadReceivedRF1] = ISNULL([LoadReceivedRF1],0.00)
      ,[LoadReceivedRF2] = ISNULL([LoadReceivedRF2],0.00)
      ,[TotalLoadReceivedRF] = ISNULL([TotalLoadReceivedRF],0.00)
      ,[GallonsSoldRF] = ISNULL([GallonsSoldRF],0.00)
      ,[BookInventoryRF] = ISNULL([BookInventoryRF],0.00)
      ,[OverShortRF] = ISNULL([OverShortRF],0.00)
      ,[ActStickReadingRF] = ISNULL([ActStickReadingRF],0.00)
      ,[WaterLevelRF] = ISNULL([WaterLevelRF],0.00)
      ,[BalForwardKerosene] = ISNULL([BalForwardKerosene],0.00)
      ,[LoadReceivedKerosene] = ISNULL([LoadReceivedKerosene],0.00)
      ,[LoadReceivedKerosene1] = ISNULL([LoadReceivedKerosene1],0.00)
      ,[LoadReceivedKerosene2] = ISNULL([LoadReceivedKerosene2],0.00)
      ,[TotalLoadReceivedKerosene] = ISNULL([TotalLoadReceivedKerosene],0.00)
      ,[GallonsSoldKerosene] = ISNULL([GallonsSoldKerosene],0.00)
      ,[BookInventoryKerosene] = ISNULL([BookInventoryKerosene],0.00)
      ,[OverShortKerosene] = ISNULL([OverShortKerosene],0.00)
      ,[ActStickReadingKerosene] = ISNULL([ActStickReadingKerosene],0.00)
      ,[WaterLevelKerosene] = ISNULL([WaterLevelKerosene],0.00)
      ,[BalForwardPropane] = ISNULL([BalForwardPropane],0.00)
      ,[LoadReceivedPropane] = ISNULL([LoadReceivedPropane],0.00)
      ,[LoadReceivedPropane1] = ISNULL([LoadReceivedPropane1],0.00)
      ,[LoadReceivedPropane2] = ISNULL([LoadReceivedPropane2],0.00)
      ,[TotalLoadReceivedPropane] = ISNULL([TotalLoadReceivedPropane],0.00)
      ,[GallonsSoldPropane] = ISNULL([GallonsSoldPropane],0.00)
      ,[BookInventoryPropane] = ISNULL([BookInventoryPropane],0.00)
      ,[OverShortPropane] = ISNULL([OverShortPropane],0.00)
      ,[ActStickReadingPropane] = ISNULL([ActStickReadingPropane],0.00)
      ,[WaterLevelPropane] = ISNULL([WaterLevelPropane],0.00)                              

                  WHERE Id = @MotorFuelInventoryId

    END
GO

Up Next
    Ebook Download
    View all
    Learn
    View all