title: SQL Update trigger doesn't work as expected
body: <p>I have a problem with the programming of an Update trigger. I want to create a trigger that copies the row I edited in a new row with the new data and a new ID. The old row should be the same with just a flag change from 0 to 1.</p>
  
  <p>The table look like this:</p>
  
  <pre><code>ID      Artikelname  PREIS        UPDATE_DATE          FLAG
  1       Tomatoe      3            14.06.2012 16:00     0
  2       Apple        1,5          12.05.2012 14:45     0
  </code></pre>
  
  <p>When I change the price of the first row, the table should look like this:</p>
  
  <pre><code>ID      Artikelname  PREIS        UPDATE_DATE          FLAG
  1       Tomatoe      3            14.06.2012 16:00     1
  2       Apple        1,5          12.05.2012 14:45     0
  1       Tomatoe      2            13.07.2012 10:45     0
  </code></pre>
  
  <p>Here is my trigger so far:</p>
  
  <pre><code>USE [TestDB]
  GO
  SET ANSI_NULLS ON
  GO
  SET QUOTED_IDENTIFIER ON
  GO
  
  ALTER TRIGGER [dbo].[Produkt_Update]
  ON [dbo].[Produkt]
  AFTER UPDATE
  AS
  BEGIN
      SET NOCOUNT ON
      DECLARE @ID int
      DECLARE @Artikelname nvarchar(50)
      DECLARE @Preis numeric
      DECLARE @Flag numeric
      DECLARE @max_id int
  
      SET @ID = (SELECT ID FROM inserted)
      SET @Artikelname = (SELECT Artikelname FROM inserted)
      SET @Preis = (SELECT Preis FROM inserted)
      SET @Flag = (SELECT Flag FROM inserted)
      SET @max_id = (SELECT MAX(ID) from dbo.Produkt)
  
  
      SET IDENTITY_INSERT dbo.Produkt ON
      INSERT INTO dbo.Produkt
      (ID,Artikelname,Preis)
      values (@max_id+1,@Artikelname,@Preis)
      SET IDENTITY_INSERT dbo.Produkt OFF
  
      UPDATE dbo.Produkt
      SET Flag = 1
      WHERE ID=@ID
  END
  </code></pre>
  
  <p>With my trigger I can create the new row but the price on the first row sill changes. I don't know how to handle this. Could you help me please?</p>