Compare Deleted and Inserted Table in SQL Server 2008

SQL Server trigger and INSERTED and DELETED table records

Tables have no inherent order. If you wish to correlate rows between inserted and deleted, you're really going to want to have an unchangeable key on the table (such as e.g. a surrogate generated by IDENTITY). Otherwise, there's no way to identify which rows correspond with each other¹.

And yes, it's normal for the trigger to fire, and for rows to be present, even when no actual data change occurred.

---

¹Indeed, there are come optimizations where UPDATEs can be decomposed into INSERT and DELETEs, and then re-composed back into UPDATEs which actually "change" different columns - overall, the correct final rows exist in the table, but which columns got updated is actually switched around.

SQL Server: Compare json and find newly added, changed and deleted records

A possible approach:

DECLARE @old nvarchar(max) = N'{
 "cifs": [
    {"cif_id":"1", "bank_code":"110"},
    {"cif_id":"2", "bank_code":"120"},
    {"cif_id":"3", "bank_code":"130"}
  ]
}'
DECLARE @new nvarchar(max) = N'{
 "cifs": [
    {"cif_id":"1", "bank_code":"111"},
    {"cif_id":"2", "bank_code":"122"},
    {"cif_id":"4", "bank_code":"140"}
  ]
}'

; WITH OldCTE AS (
   SELECT *
   FROM OPENJSON(@old, '$.cifs') WITH (
      cif_id nvarchar(1) '$.cif_id',
      bank_code nvarchar(3) '$.bank_code'
   )
), NewCTE AS (
   SELECT *
   FROM OPENJSON(@new, '$.cifs') WITH (
      cif_id nvarchar(1) '$.cif_id',
      bank_code nvarchar(3) '$.bank_code'
   )
)
SELECT
   added = (
      SELECT cif_id, bank_code
      FROM NewCTE 
      WHERE cif_id NOT IN (SELECT cif_id FROM OldCTE)
      FOR JSON AUTO
   ),
   modified = (
      SELECT n.cif_id, n.bank_code 
      FROM NewCTE n
      INNER JOIN OldCTE o ON n.cif_id = o.cif_id AND n.bank_code <> o.bank_code
      FOR JSON AUTO
   ),
   deleted = (
      SELECT cif_id, bank_code
      FROM OldCTE 
      WHERE cif_id NOT IN (SELECT cif_id FROM NewCTE)
      FOR JSON AUTO
   )
FOR JSON PATH, WITHOUT_ARRAY_WRAPPER

Result:

{
   "added":[
      {
         "cif_id":"4",
         "bank_code":"140"
      }
   ],
   "modified":[
      {
         "cif_id":"1",
         "bank_code":"111"
      },
      {
         "cif_id":"2",
         "bank_code":"122"
      }
   ],
   "deleted":[
      {
         "cif_id":"3",
         "bank_code":"130"
      }
   ]
}

How to list tables where data was inserted deleted or updated in last week

Try this one -

SELECT 
      [db_name] = d.name
    , [table_name] = SCHEMA_NAME(o.[schema_id]) + '.' + o.name
    , s.last_user_update
FROM sys.dm_db_index_usage_stats s
JOIN sys.databases d ON s.database_id = d.database_id
JOIN sys.objects o ON s.[object_id] = o.[object_id]
WHERE o.[type] = 'U'
    AND s.last_user_update IS NOT NULL
    AND s.last_user_update BETWEEN DATEADD(wk, -1, GETDATE()) AND GETDATE()

TSQL update trigger: joining inserted and deleted

With your design (that allows changing primary keys) it seems very hard to build a consistent logic.

Say, you have this table:

id   value
1    2
2    1

and issue this operation:

UPDATE  mytable
SET     id = CASE WHEN id = 1 THEN 2 ELSE 1 END,
        value = CASE WHEN value = 1 THEN 2 ELSE 1 END

which updates both records but leaves the table as this:

id   value
2    1
1    2

, which from relational point of view is similar to not changing the table at all.

The whole point of the primary keys is that they never change.

---

Related Topics