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