When updating a row, how to also keep the old values and update the linking tables?

user_9835872 0
Viewed 927

I have two tables; ItemTable and ProductTable. ProductTable's ItemID column links to ItemTable's ID column. The ID columns of both tables are primary keys and identity columns.

Like this: 

ItemTable:

ID    Col      ColOther    Latest    Time
100   'old'    'oldother'  1         <Autogenerated timestamp>


ProductTable:

ID    ItemID   Value   ValueOther   Latest   Time
12    100      'foo'   'bar'        1        <Autogenerated timestamp>

Whenever I want to manually UPDATE a row in the ItemTable, which would normally be done in just one query:

query = \
    """
    UPDATE ItemTable
    SET Col = ?, ColOther = ?
    WHERE ID = 100;
    """
cursor.execute(query, 'new', 'newother')

Instead of just doing the UPDATE like above, I would like to do these things for ItemTable:

  • Automatically UPDATE the old row to have Latest = 0
  • INSERT the row with the updated values, and Latest = 1 (Let's say this row gets the ID 250)

Then for the ProductTable:

  • Automatically UPDATE the old linking row in the ProductTable to have Latest = 0
  • And automatically INSERT the same row with the new ItemID and Latest = 1

 

This automatic INSERT and UPDATE would preferably happen with just a pure SQL query (perhaps using OUTPUT, but I'm not familiar with that), alternatively it could be implemented with some Python code. How would I go about doing this?

The wanted end result:

ItemTable:

ID    Col      ColOther     Latest    Time
100   'old'    'oldother'   0         <Autogenerated timestamp>
250   'new'    'newother'   1         <Autogenerated timestamp>


ProductTable:

ID    ItemID   Value   ValueOther   Latest   Time
12    100      'foo'   'bar'        0        <Autogenerated timestamp>
110   250      'foo'   'bar'        1        <Autogenerated timestamp>
pyodbc tsql sql-server sql python
3 Answers

Use OUTPUT for this:

I would not use triggers for this, to much activity in the trigger would result in undesired things happening when other changes occur. Instead I am using a stored procedure

I rewrote my answer after the question was changed:

-- create test tables and test data
CREATE table item
(ID int identity, 
Col varchar(99), 
ColOther varchar(99),
Latest bit default 1,
Time datetime default getdate())

INSERT item(col, colother) 
values('old','oldother')

CREATE table product
(ID int identity,
ItemID int,
[Value] varchar(20),
ValueOther varchar(20),
Latest bit default 1,
Time datetime default getdate())

INSERT product(itemid, [value], valueother)
values(1, 'foo', 'bar')

go
-- create procedure
CREATE procedure p_insert
(
  @id int,
  @col varchar(99),
  @colOther varchar(99)
)
as
BEGIN tran t
DECLARE @out table(IDold int, IDnew int)
INSERT item(col, colother, Latest)
OUTPUT @id, inserted.id INTO @out
SELECT @col, @colother, 1
FROM item
WHERE 
  id = @id
  and latest = 1

UPDATE i
SET Latest=0
FROM item i
JOIN @out o 
ON o.IDold = i.id
and i.Latest=1

DECLARE @p table
(itemid int,
[value] varchar(20),
valueother varchar(20))

UPDATE p
SET Latest=0
OUTPUT o.IDnew, deleted.[value],deleted.[valueother] 
INTO @p
FROM product p
JOIN @out o
ON p.ItemID = o.IDold
WHERE p.Latest=1

INSERT product(itemid, [value], valueother,  Latest)
SELECT itemid, value, valueother, 1
FROM @p
commit tran t

To test this: 

exec p_insert 1, 'a','b'

Note this will only work if you try to update the existing row where Latest=1.

user_8069690

The below trigger will do what you want:

CREATE TRIGGER ItemTable_OnUpdate
ON ItemTable
INSTEAD OF UPDATE
AS
BEGIN
    DECLARE @newId int, @oldId int


    INSERT INTO ItemTable(Col, ColOther, Latest)
    SELECT Col, ColOther, 1 FROM INSERTED

    --get old and new ids
    SELECT @newId=@@IDENTITY, @oldId=ID FROM INSERTED

    UPDATE ItemTable SET Latest=0 WHERE ID=@oldId


    --updating ProductTable
    INSERT INTO ProductTable (ItemID, [Value], ValueOther, Latest)
    SELECT @newId, [Value], ValueOther, 1 FROM ProductTable WHERE ItemID=@oldId


    UPDATE ProductTable SET Latest=0 WHERE ItemID=@oldId
END;

After creating the trigger any update will insert new record in ItemTable, update the prev. with Latest = 0, and update the child table as per your req.

The below is my output after the update below:

UPDATE ItemTable SET col='new', ColOther='newother' WHERE ID=12;

ItemTable:

ID  Col   ColOther  Latest  Time
12  old   oldother  0       0x00000000000007F0
13  new   newother  1       0x00000000000007EF

ProductTable:

ID  ItemID  Value   ValueOther  Latest  Time
18  12      foo     bar         0       0x00000000000007F2
19  13      foo     bar         1       0x00000000000007F1

For multiple row updates we can use the below trigger, as the above was designed as reference for the update that the requester specified (one row). I believe the below will handle multiple row updates. please try it.

create TRIGGER ItemTable_OnUpdate
ON ItemTable
INSTEAD OF UPDATE
AS
BEGIN

    Declare @s table( IdNew int,IdOld int)


MERGE INTO ItemTable AS dest
USING INSERTED AS ins ON 1=0   -- always false
    WHEN NOT MATCHED BY TARGET          -- happens for every row, because 1 is never 0
        THEN 
            INSERT (Col, ColOther, Latest)
                     VALUES (Col, ColOther, Latest)
            OUTPUT inserted.ID, ins.ID INTO @s (IdNew, IdOld);


UPDATE ItemTable SET Latest=0 WHERE ID in (select IdOld from @s)


    --updating ProductTable
    INSERT INTO ProductTable (ItemID, [Value], ValueOther, Latest)
    SELECT s.IdNew, pt.[Value], pt.ValueOther, 1 FROM @s s
        inner join ProductTable pt on pt.ItemID=s.IdOld


    UPDATE ProductTable SET Latest=0 WHERE ItemID in (select IdOld from @s)
END;
user_15538560

If you are using a modern version of SQL Server (i.e. >= SQL Server 2016 SP1)

Have you considered using a Temporal Table for this? You can read more about at Microsofts BOL

It is a very smart method for tracking changes to tables. It performs well, but altering the table structure is a bit of work.

user_1068430
Related