title: tsql update trigger1 on junction table updates main table, main table update trigger2 tries to update other columns on same row in main table - fail
body: <p>[Edit: Added table DDL, sample data, and trigger DDL at bottom of this post - long but seems simplifying this opens the doors for dismissal and obviously makes it easier for testing]</p>
  
  <p>So I have two tables, Transaction and TransactionTransactionJunc.</p>
  
  <p>When a record is updated in TransactionTransactionJunc - it updates two or three columns in the Transaction table, Amount, AmountAppliedFrom and/or AmountAppliedTo.</p>
  
  <p>The Transaction table has an update trigger that responds to updates of these 3 columns. When the Amount is updated, it updates the Balance and BalanceUndeposited columns of the same row, as well as updating a Customer table Balance as well. When the columns AmountAppliedFrom/To is updated, it updates the Balance column of the same row.</p>
  
  <p>So, within an explicit transaction, if I update a row in the TransactionTransactionJunc table, I get an error in the Messages window of SSMS: </p>
  
  <blockquote>
    <p>Msg 3930, Level 16, State 1, Procedure
    TransactionTransactionJunc_AfterUpdate, Line 143 [Batch Start Line
    109] The current transaction cannot be committed and cannot support
    operations that write to the log file. Roll back the transaction.</p>
  </blockquote>
  
  <p>In, the Results window I have this:</p>
  
  <blockquote>
    <p>ErrorNumber ErrorSeverity ErrorState ErrorLine ErrorProcedure
    ErrorMessage 207 16  1 41 Transaction_AfterUpdate Invalid column name
    'AmountDeposited'.</p>
  </blockquote>
  
  <p>I think this is a concurrency issue where the trigger on Transaction is trying to update the row, but it is still locked by the trigger on the TransactionTransactionJunc table. I'm not sure. But I KNOW that the Result window error message is wrong about the Invalid column name - the name is correct, is updating correctly via the Transaction update trigger when triggered by an SMSS sql update of the same columns the TransactionTransactionJunc table is updating. This is a false and misleading error.</p>
  
  <p>If I have the TransactionTransactionJunc trigger disabled (and before I created it) and I update the same columns (within an explicit transaction) in the Transactions table via update sql it works just fine. It has something to do with the trigger on TransactionTransactionJunc making the update to the Transactions table vs me doing it manually.</p>
  
  <p>I'm not sure how to resolve this. I do want these to happen within a transaction for business data integrity. There are PK/FK relations between these tables - so no referential integrity issue would exists - but Customer Balances and Transactional balances could get out of kilter if only one part of this process happens. In the code below, I removed the FK definitions to other tables not involved. I also did not include indexes, etc... </p>
  
  <p>Thanks!</p>
  
  <p>[Added table DDL, sample data, and trigger DDL - removed additional FK on Transaction table]</p>
  
  <p>Tables and Sample Data:</p>
  
  <pre><code>/****** Object:  Table [dbo].[Transaction]    Script Date: 9/17/2019 2:15:18 PM ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[Transaction](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [Active] [bit] NOT NULL,
      [SourceCustomerID] [bigint] NOT NULL,
      [CustomerID] [bigint] NOT NULL,
      [CustomerAccount] [nvarchar](32) NOT NULL,
      [TransactionTypeID] [bigint] NOT NULL,
      [TransactionReference] [nvarchar](32) NOT NULL,
      [PaymentTypeID] [bigint] NULL,
      [PaymentReference] [nvarchar](32) NOT NULL,
      [NSFReceipt] [bit] NOT NULL,
      [Title] [nvarchar](50) NOT NULL,
      [Date] [datetime] NOT NULL,
      [TermID] [bigint] NULL,
      [TermTitle] [nvarchar](50) NOT NULL,
      [DueInDays] [int] NOT NULL,
      [DueDate] [datetime] NOT NULL,
      [Name1] [nvarchar](50) NOT NULL,
      [Name2] [nvarchar](50) NOT NULL,
      [Name3] [nvarchar](50) NOT NULL,
      [Addr1] [nvarchar](50) NOT NULL,
      [Addr2] [nvarchar](50) NOT NULL,
      [City] [nvarchar](50) NOT NULL,
      [State] [nvarchar](2) NOT NULL,
      [ZIP] [nvarchar](5) NOT NULL,
      [Memo] [nvarchar](50) NOT NULL,
      [Amount] [decimal](19,4) NOT NULL,
      [AmountAppliedFrom] [decimal](19,4) NOT NULL,
      [AmountAppliedTo] [decimal](19,4) NOT NULL,
      [Balance] [decimal](19,4) NOT NULL,
      [AmountDeposited] [decimal](19,4) NOT NULL,
      [BalanceUndeposited] [decimal](19,4) NOT NULL,
      [SendByPrint] [bit] NOT NULL,
      [SendByEmail] [bit] NOT NULL,
      [DateCreated] [datetime] NOT NULL,
      [DateEdited] [datetime] NOT NULL,
   CONSTRAINT [PK_Transaction] PRIMARY KEY CLUSTERED 
  (
      [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_Active]  DEFAULT ((1)) FOR [Active]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_NSFReceipt]  DEFAULT ((0)) FOR [NSFReceipt]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_Amount]  DEFAULT ((0)) FOR [Amount]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_AmountAppliedFrom]  DEFAULT ((0)) FOR [AmountAppliedFrom]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_AmountAppliedTo]  DEFAULT ((0)) FOR [AmountAppliedTo]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_Balance]  DEFAULT ((0)) FOR [Balance]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_AmountDeposited]  DEFAULT ((0)) FOR [AmountDeposited]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_BalanceUndeposited]  DEFAULT ((0)) FOR [BalanceUndeposited]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_DateCreated]  DEFAULT (sysdatetime()) FOR [DateCreated]
  GO
  
  ALTER TABLE [dbo].[Transaction] ADD  CONSTRAINT [DF_Transaction_DateEdited]  DEFAULT (sysdatetime()) FOR [DateEdited]
  GO
  
  
  
  /****** Object:  Table [dbo].[TransactionTransactionJunc]    Script Date: 9/17/2019 2:20:05 PM ******/
  SET ANSI_NULLS ON
  GO
  
  SET QUOTED_IDENTIFIER ON
  GO
  
  CREATE TABLE [dbo].[TransactionTransactionJunc](
      [ID] [bigint] IDENTITY(1,1) NOT NULL,
      [TransactionAppliedFromID] [bigint] NOT NULL,
      [TransactionAppliedToID] [bigint] NOT NULL,
      [Amount] [decimal](19,4) NOT NULL,
      [DateCreated] [datetime] NOT NULL,
      [DateEdited] [datetime] NOT NULL,
   CONSTRAINT [PK_TransactionTransactionJunc] PRIMARY KEY CLUSTERED 
  (
      [ID] ASC
  )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]
  ) ON [PRIMARY]
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc] ADD  CONSTRAINT [DF_TransactionTransactionJunc_DateCreated]  DEFAULT (sysdatetime()) FOR [DateCreated]
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc] ADD  CONSTRAINT [DF_TransactionTransactionJunc_DateEdited]  DEFAULT (sysdatetime()) FOR [DateEdited]
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc]  WITH CHECK ADD  CONSTRAINT [FK_TransactionTransactionJunc_Transaction_TransactionAppliedFrom] FOREIGN KEY([TransactionAppliedFromID])
  REFERENCES [dbo].[Transaction] ([ID])
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc] CHECK CONSTRAINT [FK_TransactionTransactionJunc_Transaction_TransactionAppliedFrom]
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc]  WITH CHECK ADD  CONSTRAINT [FK_TransactionTransactionJunc_Transaction_TransactionAppliedTo] FOREIGN KEY([TransactionAppliedToID])
  REFERENCES [dbo].[Transaction] ([ID])
  GO
  
  ALTER TABLE [dbo].[TransactionTransactionJunc] CHECK CONSTRAINT [FK_TransactionTransactionJunc_Transaction_TransactionAppliedTo]
  GO
  
  /* Data */
  SET IDENTITY_INSERT [dbo].[Transaction] ON 
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (35841, 1, 3, 7958, N'999999', 1, N'24552', NULL, N'', 0, N'EMS Fee 2018', CAST(N'2018-07-01T00:00:00.000' AS DateTime), 28, N'Net 62', 62, CAST(N'2018-09-01T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'', 50.0000, 50.0000, 0.0000, 0.0000, 0.0000, 0.0000, 1, 0, CAST(N'2018-06-13T20:11:11.000' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (35842, 1, 3, 7958, N'999999', 1, N'24553', NULL, N'', 0, N'EMS Fee 2017 - 1st Half', CAST(N'2017-07-01T00:00:00.000' AS DateTime), 28, N'Net 62', 62, CAST(N'2017-09-01T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'', 25.0000, 25.0000, 0.0000, 0.0000, 0.0000, 0.0000, 1, 0, CAST(N'2018-07-04T10:34:12.000' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (35843, 1, 3, 7958, N'999999', 1, N'24554', NULL, N'', 0, N'EMS Fee 2017', CAST(N'2017-07-01T00:00:00.000' AS DateTime), 29, N'Net 243', 243, CAST(N'2018-03-01T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'VOID:', 0.0000, 0.0000, 0.0000, 0.0000, 0.0000, 0.0000, 1, 0, CAST(N'2018-07-04T10:35:03.000' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (42598, 1, 3, 7958, N'999999', 3, N'FC 2', NULL, N'', 0, N'Late Fee 2018 - for EMS Fee 2018', CAST(N'2018-09-04T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-09-04T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'Finance Charge', 15.0000, 15.0000, 0.0000, 0.0000, 0.0000, 0.0000, 1, 0, CAST(N'2018-09-04T09:31:18.000' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (44963, 1, 3, 7958, N'999999', 3, N'FC 2366', NULL, N'', 0, N'Late Fee 2018 - for EMS Fee 2018', CAST(N'2018-09-24T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-09-24T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'Finance Charge', 15.0000, 10.0000, 0.0000, 5.0000, 0.0000, 0.0000, 1, 0, CAST(N'2018-09-24T16:35:44.000' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62197, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2018-10-01T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-10-01T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 5.0000, 0.0000, 5.0000, 0.0000, 0.0000, 5.0000, 1, 0, CAST(N'2019-09-14T16:04:04.783' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62215, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2018-10-01T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-10-01T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 50.0000, 0.0000, 50.0000, 0.0000, 0.0000, 50.0000, 1, 0, CAST(N'2019-09-14T20:04:16.160' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62216, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2018-10-02T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-10-02T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 20.0000, 0.0000, 20.0000, 0.0000, 0.0000, 20.0000, 1, 0, CAST(N'2019-09-14T20:04:16.160' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62217, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2018-10-03T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2018-10-03T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 10.0000, 0.0000, 0.0000, 10.0000, 0.0000, 10.0000, 1, 0, CAST(N'2019-09-14T20:04:16.160' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62252, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2019-01-01T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2019-01-01T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 30.0000, 0.0000, 0.0000, 30.0000, 0.0000, 30.0000, 1, 0, CAST(N'2019-09-15T06:42:28.610' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62253, 1, 3, 7958, N'999999', 7, N'', 7, N'', 0, N'Receipt-Cash', CAST(N'2019-08-02T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2019-08-02T00:00:00.000' AS DateTime), N'', N'', N'', N'', N'', N'', N'', N'', N'', 5.0000, 0.0000, 0.0000, 5.0000, 0.0000, 5.0000, 1, 0, CAST(N'2019-09-15T06:42:28.610' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  INSERT [dbo].[Transaction] ([ID], [Active], [SourceCustomerID], [CustomerID], [CustomerAccount], [TransactionTypeID], [TransactionReference], [PaymentTypeID], [PaymentReference], [NSFReceipt], [Title], [Date], [TermID], [TermTitle], [DueInDays], [DueDate], [Name1], [Name2], [Name3], [Addr1], [Addr2], [City], [State], [ZIP], [Memo], [Amount], [AmountAppliedFrom], [AmountAppliedTo], [Balance], [AmountDeposited], [BalanceUndeposited], [SendByPrint], [SendByEmail], [DateCreated], [DateEdited]) VALUES (62254, 1, 3, 7958, N'999999', 1, N'999001', NULL, N'', 0, N'EMS Fee 2019', CAST(N'2019-09-15T00:00:00.000' AS DateTime), NULL, N'', 0, CAST(N'2019-09-15T00:00:00.000' AS DateTime), N'Sample Customer Name', N'', N'', N'1234 Fake St.', N'', N'Sample City', N'SS', N'99999', N'testing', 50.0000, 0.0000, 0.0000, 50.0000, 0.0000, 50.0000, 1, 0, CAST(N'2019-09-15T06:42:28.610' AS DateTime), CAST(N'2019-09-17T08:19:13.527' AS DateTime))
  GO
  SET IDENTITY_INSERT [dbo].[Transaction] OFF
  GO
  SET IDENTITY_INSERT [dbo].[TransactionTransactionJunc] ON 
  GO
  INSERT [dbo].[TransactionTransactionJunc] ([ID], [TransactionAppliedFromID], [TransactionAppliedToID], [Amount], [DateCreated], [DateEdited]) VALUES (27300, 62215, 35841, 50.0000, CAST(N'2019-09-17T08:19:13.230' AS DateTime), CAST(N'2019-09-17T08:19:13.230' AS DateTime))
  GO
  INSERT [dbo].[TransactionTransactionJunc] ([ID], [TransactionAppliedFromID], [TransactionAppliedToID], [Amount], [DateCreated], [DateEdited]) VALUES (27301, 62197, 42598, 5.0000, CAST(N'2019-09-17T08:19:13.230' AS DateTime), CAST(N'2019-09-17T08:19:13.230' AS DateTime))
  GO
  INSERT [dbo].[TransactionTransactionJunc] ([ID], [TransactionAppliedFromID], [TransactionAppliedToID], [Amount], [DateCreated], [DateEdited]) VALUES (27302, 62216, 42598, 10.0000, CAST(N'2019-09-17T08:19:13.230' AS DateTime), CAST(N'2019-09-17T08:19:13.230' AS DateTime))
  GO
  INSERT [dbo].[TransactionTransactionJunc] ([ID], [TransactionAppliedFromID], [TransactionAppliedToID], [Amount], [DateCreated], [DateEdited]) VALUES (27303, 62216, 44963, 10.0000, CAST(N'2019-09-17T08:19:13.230' AS DateTime), CAST(N'2019-09-17T08:19:13.230' AS DateTime))
  GO
  SET IDENTITY_INSERT [dbo].[TransactionTransactionJunc] OFF
  GO
  </code></pre>
  
  <p>Triggers:</p>
  
  <pre><code>/*  
      Trigger to Update the Balances in Transaction table when a Transaction is Updated - such as
      applying credit Transactions to debit Transactions and Deposits. Customer Balance is also updated.
  */
  
  CREATE TRIGGER [dbo].[Transaction_AfterUpdate]
  ON dbo.[Transaction]
  AFTER Update
  AS
  IF (ROWCOUNT_BIG() = 0)  -- Must be the first statement. Even after set nocount on it will then return 0 and drop out of the trigger
  RETURN;
  set nocount on
  
  begin try   
      begin tran
          /* Update the Transaction DateEdited column */
          UPDATE dbo.[Transaction]
          SET DateEdited = SYSDATETIME()
          FROM Inserted i
          WHERE dbo.[Transaction].ID = i.ID
  
          if (UPDATE([Amount]) or UPDATE([AmountAppliedFrom]) or UPDATE([AmountAppliedTo]) or UPDATE([AmountDeposited]))
              /* Calulate differences in the Amounts and store temporarily */  -- I think this is working fine - maybe needs more columns?
              select  i.*,
                  (i.Amount - d.Amount) as AmountDiff,
                  (d.AmountAppliedFrom - i.AmountAppliedFrom) as AmountAppliedFromDiff,   -- These are intentionally reversed (d-i vs i-d)
                  (d.AmountAppliedTo - i.AmountAppliedTo) as AmountAppliedToDiff,         -- It made to logic cleaner below
                  (d.AmountDeposited - i.AmountDeposited) as AmountDepositedDiff
              into    #tmp1
              from    inserted i join deleted d on i.ID = d.ID        
  
          if (UPDATE([Amount]))
              begin
                  if ((select count(*) from #tmp1 where AmountDiff &lt;&gt; 0 and AmountDeposited &gt; 0) &gt; 0)
                      Throw 50000, 'Business Rule Error: Cannot change Receipt Amount when any portion of it has been Deposited', 1
                  else
                      begin
                          update Customer
                          set Balance +=
                              (
                                  select ISNULL(SUM(CASE WHEN t.TransactionTypeID in (1, 2, 3, 4) THEN t.AmountDiff when t.TransactionTypeID in (5, 6, 7) then t.AmountDiff * -1 else 0 END), 0)
                                  from #tmp1 t where t.CustomerID = Customer.ID
                              );
                          update [Transaction]
                          set Balance += 
                              (
                                  select isnull(sum(t.AmountDiff), 0)
                                  from #tmp1 t
                                  where t.ID = [Transaction].ID
                              ),
                              BalanceUndeposited +=
                              (
                                  select isnull(sum(t.AmountDiff * (case when t.TransactionTypeID in (1, 2, 3, 4, 5, 6) then 0
                                  when t.TransactionTypeID = 7 then 1 end)), 0)  -- Only Receipts need AmountDeposited and BalanceUndeposited
                                  from #tmp1 t
                                  where t.ID = [Transaction].ID
                              )
                      end
              end
  
          if (UPDATE([AmountAppliedFrom]) or UPDATE([AmountAppliedTo]))
              update [Transaction]
              set Balance +=  ((select sum(AmountAppliedFromDiff) from #tmp1 where ID = [Transaction].ID) + 
                               (select sum(AmountAppliedToDiff) from #tmp1 where ID = [Transaction].ID))
              from #tmp1 i
              where [Transaction].ID = i.ID
          if (UPDATE([AmountDeposited]))
              update [Transaction]
              set BalanceUndeposited += (select sum(AmountDepositedDiff) from #tmp1 where ID = [Transaction].ID)
              from #tmp1 i            
              where [Transaction].ID = i.ID
          drop table if exists #tmp1
      commit tran
  end try
  begin catch
      exec usp_Report_Error
      drop table if exists #tmp1
      IF (XACT_STATE()) = -1  
      BEGIN  
          PRINT  N'The transaction is in an uncommittable state.' +  
                  'Rolling back transaction.'  
          ROLLBACK TRANSACTION;  
      END;  
  
      -- Test if the transaction is committable.  
      IF (XACT_STATE()) = 1  
      BEGIN  
          PRINT N'The transaction is committable.' +  
              'Committing transaction.'  
          COMMIT TRANSACTION;     
      END;        
  end catch
  </code></pre>
  
  <p>and</p>
  
  <pre><code>/*  
      Trigger to Update the AmountAppliedFrom/To in the Transaction table when a TransactionTransactionJunc record is Updated
  */
  
  CREATE TRIGGER [dbo].[TransactionTransactionJunc_AfterUpdate]
  ON dbo.[TransactionTransactionJunc]
  AFTER Update
  AS
  IF (ROWCOUNT_BIG() = 0)  -- Must be the first statement. Even after set nocount on it will then return 0 and drop out of the trigger
  RETURN;
  set nocount on
  
  begin try       
      /* Update the TransactionTransactionJunc DateEdited column */
      UPDATE dbo.TransactionTransactionJunc
      SET DateEdited = SYSDATETIME()
      FROM Inserted i
      WHERE dbo.TransactionTransactionJunc.ID = i.ID
  
      if (UPDATE(Amount))
          /* Calulate differences in the Amount and store temporarily */
          select  i.*, (i.Amount - d.Amount) as AmountDiff
          into    #tmp1
          from    inserted i join deleted d on i.ID = d.ID        
  
      /* Amount change is handled differently when only the Amount changes verses when the TransactionAppliedFrom/To also changes */
      if (UPDATE([Amount]) and not UPDATE(TransactionAppliedFromID) and not UPDATE(TransactionAppliedToID))
          begin
              update  [Transaction]
              set     AmountAppliedTo +=
                  (
                      select isnull(sum(t.AmountDiff), 0) 
                      from #tmp1 t
                      where [Transaction].ID = t.TransactionAppliedFromID
                  ), AmountAppliedFrom +=
                  (
                      select isnull(sum(t.AmountDiff), 0) 
                      from #tmp1 t
                      where [Transaction].ID = t.TransactionAppliedToID
                  )
          end
      /* Change Transaction that was AppliedFromID to a different Transaction */
      if (UPDATE(TransactionAppliedFromID) and not UPDATE(TransactionAppliedToID))
          begin
              /* Undo the previous Transaction AmountAppliedTo */
              update  [Transaction]
              set     AmountAppliedTo -= -- Reversing here, so subtract
                  (
                      select isnull(sum(d.Amount), 0)
                      from deleted d
                      where d.TransactionAppliedFromID = [Transaction].ID
                  )
              /* Update with the replacement Transaction record */
              update  [Transaction]
              set     AmountAppliedTo += -- Adding to the newly referenced Transaction - so add
                  (
                      select ISNULL(sum(i.Amount), 0)
                      from inserted i
                      where i.TransactionAppliedFromID = [Transaction].ID
                  )
              /* If Amount also changed then also update the Transaction (TransactionID=AppliedToID) AmountAppliedFrom value */
              if (UPDATE(Amount))
                  update  [Transaction]
                  set     AmountAppliedFrom +=
                      (
                          select  isnull(sum(t.AmountDiff), 0)
                          from    #tmp1 t
                          where   t.TransactionAppliedToID = [Transaction].ID
                      )
          end
      /* Change Transaction that was AppliedToID to a different Transaction */
      if (UPDATE(TransactionAppliedToID) and not UPDATE(TransactionAppliedFromID))
          begin
              /* Undo the previous Transaction AmountAppliedFrom */
              update  [Transaction]
              set     AmountAppliedFrom -= -- Reversing here, so subtract
                  (
                      select isnull(sum(d.Amount), 0)
                      from deleted d
                      where d.TransactionAppliedToID = [Transaction].ID
                  )
              /* Update with the replacement Transaction record */
              update  [Transaction]
              set     AmountAppliedFrom += -- Adding to the newly referenced Transaction - so add
                  (
                      select ISNULL(sum(i.Amount), 0)
                      from inserted i
                      where i.TransactionAppliedToID = [Transaction].ID
                  )
              /* If Amount also changed then also update the Transaction (TransactionID=AppliedFromID) AmountAppliedFrom value */
              if (UPDATE(Amount))
                  update  [Transaction]
                  set     AmountAppliedTo +=
                      (
                          select  isnull(sum(t.AmountDiff), 0)
                          from    #tmp1 t
                          where   t.TransactionAppliedFromID = [Transaction].ID
                      )
          end
          /* Change Transaction that was AppliedToID and AppliedFromID to different Transactions */
          if (UPDATE(TransactionAppliedToID) and UPDATE(TransactionAppliedFromID))
          begin
              /* Undo the previous Transaction AmountAppliedFrom */
              update  [Transaction]
              set     AmountAppliedFrom -= -- Reversing here, so subtract
                  (
                      select isnull(sum(d.Amount), 0)
                      from deleted d
                      where d.TransactionAppliedToID = [Transaction].ID
                  )
              /* Undo the previous Transaction AmountAppliedTo */
              update  [Transaction]
              set     AmountAppliedTo -= -- Reversing here, so subtract
                  (
                      select isnull(sum(d.Amount), 0)
                      from deleted d
                      where d.TransactionAppliedFromID = [Transaction].ID
                  )
  
              /* Update with the replacement Transaction records */
              update  [Transaction]
              set     AmountAppliedFrom += -- Adding to the newly referenced Transaction - so add
                  (
                      select ISNULL(sum(i.Amount), 0)
                      from inserted i
                      where i.TransactionAppliedToID = [Transaction].ID
                  )               
              update  [Transaction]
              set     AmountAppliedTo += -- Adding to the newly referenced Transaction - so add
                  (
                      select ISNULL(sum(i.Amount), 0)
                      from inserted i
                      where i.TransactionAppliedFromID = [Transaction].ID
                  )
              /* If Amount also changed, in this case it doesn't matter. Previous records are not being adjusted,
                  the updating of the replacement Transactions with the inserted.Amount takes care of it for both
                  sides of the update */
          end
      drop table if exists #tmp1  
  end try
  begin catch
      exec usp_Report_Error
      drop table if exists #tmp1
      IF (XACT_STATE()) = -1  
      BEGIN  
          PRINT  N'The transaction is in an uncommittable state.' +  
                  'Rolling back transaction.'  
          ROLLBACK TRANSACTION;  
      END;  
  
      -- Test if the transaction is committable.  
      IF (XACT_STATE()) = 1  
      BEGIN  
          PRINT N'The transaction is committable.' +  
              'Committing transaction.'  
          COMMIT TRANSACTION;     
      END;        
  end catch
  </code></pre>