USE [MABBSolutions] GO /****** Object: Trigger [dbo].[trgAfterUpdate] Script Date: 5/27/2015 1:32:22 PM ******/ SET ANSI_NULLS ON GO SET QUOTED_IDENTIFIER ON GO -- Batch submitted through debugger: SQLQuery3.sql|7|0|C:\Users\ZingSoft\AppData\Local\Temp\~vs242B.sql ALTER TRIGGER [dbo].[trgAfterUpdate] ON [dbo].[FourLevel] FOR UPDATE AS SET NOCOUNT ON IF(UPDATE(OpeningBalanceDebit) OR UPDATE(OpeningBalanceCredit)) BEGIN DECLARE @CurrentDr decimal(26,2); DECLARE @CurrentCr decimal(26,2); DECLARE @AccountCode nvarchar(12); DECLARE @OpeningDebit decimal(26,2); DECLARE @OpeningCredit decimal(26,2); SELECT @AccountCode = i.AccountCodes FROM inserted i; SELECT @OpeningDebit = i.OpeningBalanceDebit FROM inserted i; SELECT @OpeningCredit = i.OpeningBalanceCredit FROM inserted i; SELECT @CurrentDr = CurrentBalanceDebit FROM [dbo].[FourLevel] WHERE AccountCodes = @AccountCode; SELECT @CurrentCr = CurrentBalanceCredit FROM [dbo].[FourLevel] WHERE AccountCodes = @AccountCode; IF (@OpeningDebit != 0) BEGIN IF(@CurrentDr != 0) BEGIN SET @OpeningDebit = @CurrentDr + @OpeningDebit; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = @OpeningDebit, CurrentBalanceCredit = 0 WHERE AccountCodes = @AccountCode; END ELSE BEGIN IF (@CurrentCr >= @OpeningDebit) BEGIN SET @CurrentCr = @CurrentCr - @OpeningDebit; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = 0, CurrentBalanceCredit = @CurrentCr WHERE AccountCodes = @AccountCode; END ELSE BEGIN SET @CurrentDr = @OpeningDebit - @CurrentCr; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = @CurrentDr, CurrentBalanceCredit = 0 WHERE AccountCodes = @AccountCode; END END END IF (@OpeningCredit != 0) BEGIN IF (@CurrentCr != 0) BEGIN SET @CurrentCr = @CurrentCr + @OpeningCredit; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = 0, CurrentBalanceCredit = @CurrentCr WHERE AccountCodes = @AccountCode; END ELSE BEGIN IF(@CurrentDr >= @OpeningCredit) BEGIN SET @CurrentDr = @CurrentDr - @OpeningCredit; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = @CurrentDr, CurrentBalanceCredit = 0 WHERE AccountCodes = @AccountCode; END ELSE BEGIN SET @CurrentCr = @OpeningCredit - @CurrentDr; UPDATE [dbo].[FourLevel] SET CurrentBalanceDebit = 0, CurrentBalanceCredit = @CurrentCr WHERE AccountCodes = @AccountCode; END END END END
var
This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)