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

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

Solution

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