title: SQL Server table trigger not inserting value
body: <p>I am creating an after insert table trigger that inserts into another table. All columns work as expected, but when it comes to one particular column (<code>T_W_A.WorkAreaBuffer</code>) that has geographic data in it, it comes back null and I don't understand why. When I take the select statement out of the trigger, all columns work as expected. Here is a copy of the information stored in <code>T_W_A.WorkAreaBuffer</code>:</p>
  
  <pre><code>POLYGON((-80.0705872065121 33.0028430262796,-80.0706042845348 33.0027090648411,-80.0706520501637 33.0025804848199,-80.0707286676788 33.0024622274388,-80.0708311926384 33.0023588372123,-80.0709556850437 33.0022742873109,-80.0710973607543 33.0022118268834,-80.0712507753344 33.0021738562025,-80.0714100332675 33.0021618344308,-80.0715690144994 33.0021762235502,-80.071721609607 33.0022164706098,-80.0718619545597 33.002281028973,-80.071984656053 33.0023674177484,-80.0720849987596 33.0024723171218,-80.0721591265326 33.0025916959256,-80.072204190601 33.0027209665457,-80.072218459058 33.0028551612142,-80.0722013834361 33.0029891229132,-80.0721536198053 33.0031177035549,-80.0720770035815 33.0032359618221,-80.0719744790095 33.0033393530653,-80.0718499860293 33.0034239039594,-80.0717083088688 33.0034863652042,-80.0715548921845 33.0035243364028,-80.0713956318132 33.0035363583138,-80.0712366481804 33.0035219689339,-80.0710840510747 33.0034817212538,-80.0709437048309 33.0034171620046,-80.0708210029499 33.0033307722124,-80.0707206608182 33.0032258718464,-80.0706465344951 33.0031064922252,-80.0706014725309 33.0029772210874,-80.0705872065121 33.0028430262796))
  </code></pre>
  
  <p>Here is the table structure I am inserting into:</p>
  
  <pre><code>CREATE TABLE [dbo].[training_grades]
  (
      [Id] [BIGINT],
      [Number] [VARCHAR](24) NULL,
      [FirstTimeScore] INT,
      [prodarea] NVARCHAR(MAX) NULL
  )
  </code></pre>
  
  <p>Here is the Ticket structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[Ticket]
      (
          [TicketId] [BIGINT] primary key,
          [Number] [VARCHAR](24) NULL
      )
  </code></pre>
  
  <p>Here is the T_W_A structure:</p>
  
  <pre><code>CREATE TABLE [dbo].[T_W_A]
          (
              [TicketId] [BIGINT] primary key foreign key,
              [WorkAreaBuffer] [TEXT] NULL
          )
  </code></pre>
  
  <p>Here is the trigger:</p>
  
  <pre><code>CREATE TRIGGER TRG_InsertSyncEmp 
  ON Ticket
  AFTER INSERT 
  AS
  BEGIN
      INSERT INTO [dbo].[training_grades]
          SELECT 
              i.TicketId, 
              Number,
              CASE WHEN ia.FirstTime = 1 THEN 1 ELSE 0 END AS FirstTimeScore,
              tw.workareabuffer AS prodarea
          FROM 
              INSERTED i 
          LEFT JOIN 
              itemAtribute ia ON i.TicketId = ia.ItemId
          JOIN 
              T_W_A tw ON i.TicketId = tw.TicketId
  END
  </code></pre>
  
  <p><code>tw.workareabuffer</code> is a text data type and from what I've read, you cannot insert <code>text</code> fields using a trigger. </p>
  
  <p>My next step was to create a view of the T_W_A and cast the text field to a nvarchar(max) type, but the results were the same. </p>
  
  <p>This leads to me believe that there is something wrong with the join itself, but like I said, when I take the select statement out of the trigger, it works as expected. </p>
  
  <p>Any suggestions to isolate the problem?</p>