CODEWITHSUNDEEP
sqlsql-servertriggers
Eray Geveci
Eray Geveci

Reputation: 1129

SQL Update trigger doesn't work as expected

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.

The table look like this:

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

When I change the price of the first row, the table should look like this:

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

Here is my trigger so far:

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

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?

Upvotes: 1

Views: 2294

Answers (3)

Joe G Joseph
Joe G Joseph

Reputation: 24046

Please try this and let me know

CREATE TRIGGER [dbo].[Produkt_Update]
   ON [dbo].[Produkt]
   AFTER  UPDATE
AS
BEGIN
 SET  NOCOUNT ON;

    DECLARE @MAX_ID INT;
    SELECT @MAX_ID=MAX(ID) FROM [Produkt];

    declare @tmp Table(ID  int,     Artikelname  varchar(200),
    PREIS varchar(200),UPDATE_DATE datetime,      FLAG bit)

    insert into @tmp
    select ID,Artikelname,PREIS,UPDATE_DATE,1 [flag] from deleted;

    delete T from [Produkt] T JOIN @tmp I
    ON T.ID=I.ID

SET IDENTITY_INSERT   [Produkt] ON
INSERT INTO [Produkt] (ID,Artikelname,PREIS,UPDATE_DATE,FLAG)
SELECT @MAX_ID+ROW_NUMBER() OVER(ORDER BY ID) [ID],Artikelname,PREIS,GETDATE(),0 
FROM INSERTED
union all
select * from @tmp

 SET IDENTITY_INSERT dbo.Produkt OFF
 SET  NOCOUNT OFF;
END;

Upvotes: 4

podiluska
podiluska

Reputation: 51494

You need to insert the price (preis) from the deleted table, not the inserted table

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Replace 

 SET @ID = (SELECT ID FROM inserted)

with

SET @ID = (SELECT ID FROM deleted)

Upvotes: -1

Related Questions