title: T-SQL Insert Trigger to insert,update on if condition on multiple tables
body: <p>I'm having a few issues trying to resolve an SQL Trigger to automatically set a user to blocked &amp; create the block record including date in another table, if their Due date is equal to a set date. </p>
  
  <p>The issue is that when the trigger is set off by an insert, the print statements are executed and the insert occurs, but the insert into the table does not, or the update statement ? Can anyone explain why ?</p>
  
  <p>Note: Both the insert and the Update statement are fine when executed by themselves.</p>
  
  <p>ACCOUNT TABLE</p>
  
  <pre><code>CREATE TABLE [dbo].[Account](
  [AccountNo] [int] IDENTITY(1,1) NOT NULL,
  [CustomerNo] [int] NOT NULL,
  [PaymentNo] [int] NULL,
  [CreditNo] [int] NULL,
  [BlockID] [dbo].[number] NULL,
  [Balence] [dbo].[currency] NOT NULL,
  [AmountDue] [dbo].[currency] NOT NULL,
  [DueDate] [dbo].[dates] NULL,
  [AutherisedBy] [nvarchar](50) NOT NULL,
  [DateCreated] [date] NOT NULL,
  </code></pre>
  
  <p>BLOCKEDUSER TABLE</p>
  
  <pre><code>CREATE TABLE [dbo].[BlockedUsers](
  [BlockID] [int] IDENTITY(1,1) NOT NULL,
  [DateEnforced] [dbo].[dates] NOT NULL,
  [Blocked] [dbo].[switch] NOT NULL,
  </code></pre>
  
  <p>TRIGGER</p>
  
  <pre><code>ALTER TRIGGER [dbo].[Add_Blocked_User]
  ON [dbo].[Account]
  FOR INSERT
  AS  
  BEGIN
  SET NOCOUNT ON;
  
  Declare @ID int
  Select @ID = [AccountNo] from inserted  
  If(Select [DueDate] from inserted) = '2011-01-01'
  
  INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
  VALUES (GETDATE(),1)
  PRINT 'New Block Date Added'
  
  UPDATE Account 
  Set BlockID = IDENT_CURRENT('BlockID')
  where @ID = @ID
  PRINT 'Account Blocked'
  
  END
  
  GO
  </code></pre>
  
  <p>Fully Working Example : Completed using Help Below.</p>
  
  <pre><code>ALTER TRIGGER [dbo].[Add_Blocked_User]
  ON [dbo].[Account]
  AFTER INSERT
  AS 
  BEGIN
  
  SET NOCOUNT ON;
  
  Declare @ID int
  Select @ID = [AccountNo] from inserted  
   If(Select [DueDate] from inserted)Not Between (select CONVERT(date, getdate() - 30)) And (select CONVERT(date, getdate()))
  Begin
      INSERT INTO dbo.BlockedUsers(DateEnforced,Blocked)
      VALUES (GETDATE(),1)
      PRINT 'New Block Date Added'
  
      UPDATE Account 
      Set BlockID = (Select Max(BlockID) From BlockedUsers)
      where [AccountNo] = (Select [AccountNo] from inserted)  
      PRINT 'Account Blocked'
   End
  
   END
  
  GO
  </code></pre>