If the data is updated from a table, the data should be inserted in another table in SQL Server 2017

Question

If the data is updated from a table, the data should be inserted in another table in sqlserver2017. I will be creating a SP with this

Table 1:dbo.testing_updated

Table 2:dbo.testing_insert

If any data is updated in table 1 using the update query, then the updated column name should be inserted in table 2.

I tried,

update dbo.testing_updated set name='a' where name='suba';

IF (@@ROWCOUNT > 0)
    BEGIN
        insert into dbo.testing_insert values('1')
    END

The value should be inserted in the table only if any data is updated. Else nothing should be updated. In my case, everytime data is inserted irrespective of update

SELECT @@version; gives me Microsoft SQL Server 2017

And yet your title still says MySQL while you added the sql server tag back after removal. Which is it? — SMor, Feb 18 '21 at 13:43
And yet again - your question specifically mentions sql server 2017 but you removed the tag. But does MySQL even have `@@ROWCOUNT`? — SMor, Feb 18 '21 at 13:48
Please add the output of this SQL statement to your question: `SELECT @@version;` (this should avoid confusion about what you use...) — Luuk, Feb 18 '21 at 13:50
Please read: [Differences between MySQL and SQL Server](https://stackoverflow.com/questions/10616/differences-between-mysql-and-sql-server) — Luuk, Feb 18 '21 at 13:54
Sounds like you may want an `AFTER UPDATE` trigger on the table in question where you can use both `INSERTED` (new values) and `DELETED` (old values) resultsets. — critical_error, Feb 18 '21 at 17:49

WAMLeslie · Accepted Answer · 2021-02-19T13:48:49.983

I'm not sure of your tables schemas, but you could do this with a trigger on your Table 1 to INSERT into Table 2 on UPDATE. Something like this will work.

DROP TABLE IF EXISTS testing_updated
DROP TABLE IF EXISTS testing_insert

CREATE TABLE testing_updated
(
  [NAME] NVARCHAR(50)
)
GO

CREATE TABLE testing_insert
(
  [name] NVARCHAR(50),
  InsertedValue SMALLINT
)
GO

CREATE TRIGGER tblUpdateTrigger ON testing_updated
AFTER UPDATE
AS
BEGIN
  INSERT INTO testing_insert ([name], InsertedValue)
  SELECT T.[name], 1
  FROM  testing_updated T
  INNER JOIN inserted i ON T.[name]=I.[name] 
END
GO

EDIT:

Based on your comment, it sounds like you want to update multiple tables. You could do this by creating multiple triggers on the same source table with conditions which would insert into separate tables. I'm not entirely clear on what your required outcome is, but see below. I think some variation of this would work for you.

DROP TABLE IF EXISTS testing_updated
DROP TABLE IF EXISTS testing_insert1
DROP TABLE IF EXISTS testing_insert2

CREATE TABLE testing_updated
(
  [NAME] NVARCHAR(50)
)
GO

CREATE TABLE testing_insert1
(
  [name] NVARCHAR(50),
  InsertedValue SMALLINT
)
GO

CREATE TABLE testing_insert2
(
  [name] NVARCHAR(50),
  InsertedValue SMALLINT
)
GO

CREATE TRIGGER tblUpdateTrigger1 ON testing_updated
AFTER UPDATE,INSERT
AS
BEGIN
  INSERT INTO testing_insert1 ([name], InsertedValue)
  SELECT T.[name], 1
  FROM  testing_updated T
  INNER JOIN inserted i ON T.[name]=I.[name] AND I.[NAME]='abc'
END
GO

CREATE TRIGGER tblUpdateTrigger2 ON testing_updated
AFTER UPDATE,INSERT
AS
BEGIN
  INSERT INTO testing_insert2 ([name], InsertedValue)
  SELECT T.[name], 1
  FROM  testing_updated T
  INNER JOIN inserted i ON T.[name]=I.[name] AND I.[NAME]='xyz'
END
GO

INSERT INTO testing_updated VALUES ('abc')

SELECT * FROM testing_updated
SELECT * FROM testing_insert1
SELECT * FROM testing_insert2

INSERT INTO testing_updated VALUES ('xyz')

SELECT * FROM testing_updated
SELECT * FROM testing_insert1
SELECT * FROM testing_insert2

Hi. Thanks for your reply. I will be updating in multiple tables. For sample i used only one table to update. My solution should be like if any data change has happened through the update query, then i will insert the field name in another table . — Subasri Kalyankumar, Feb 19 '21 at 09:51

score 1 · Answer 2 · answered Feb 18 '21 at 22:40

Have a look into temporal tables. They do an automatic backup of the record on every update/delete and you can relatively simple ask how a record looked like at any given point in time.

Here an (unrelated) example of how to create such a table:

CREATE TABLE [dbo].[TestOrder](
    [TestOrder_ID] [int] IDENTITY(1,1) NOT NULL,
    [TestOrder_OrderNo] [varchar](15) NOT NULL,
    [TestOrder_Details] [nvarchar](max) NOT NULL,
    [TestOrderState_ID] [tinyint] NOT NULL,
    [ValidFromUtc] [datetime2](7) GENERATED ALWAYS AS ROW START NOT NULL,
    [ValidToUtc] [datetime2](7) GENERATED ALWAYS AS ROW END NOT NULL,
 CONSTRAINT [PK_TestOrder] PRIMARY KEY CLUSTERED 
(
    [TestOrder_ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = ON) ON [PRIMARY],
    PERIOD FOR SYSTEM_TIME ([ValidFromUtc], [ValidToUtc])
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
WITH
(
SYSTEM_VERSIONING = ON ( HISTORY_TABLE = [dbo].[TestOrder_Archive] )
)

Hi. Thanks for your reply. I will be updating in multiple tables. For sample i used only one table to update. My solution should be like if any data change has happened through the update query, then i will insert the field name in another table . — Subasri Kalyankumar, Feb 19 '21 at 09:51

If the data is updated from a table, the data should be inserted in another table in SQL Server 2017

2 Answers2