You can try this using Triggers. See if the lab below helps you:
1 - Create Two Tables (Table1 and Table2)
CREATE TABLE dbo.Table1 (
id INT,
descricao NVARCHAR(40)
)
CREATE TABLE dbo.Table2 (
id INT,
descricao NVARCHAR(40)
)
2 - Insert Records for Testing:
INSERT INTO Table1 (id,descricao) VALUES (1,'Registro 1 TABELA 1');
INSERT INTO Table1 (id,descricao) VALUES (2,'Registro 2 TABELA 1');
INSERT INTO Table2 (id,descricao) VALUES (1,'Registro 1');
INSERT INTO Table2 (id,descricao) VALUES (2,'Registro 2');
3 - Create an UPDATE Trigger for the Table2 table:
CREATE TRIGGER TGR_TABLE2_AI
ON dbo.Table2
AFTER UPDATE
AS
BEGIN
DECLARE
@ID INT
SELECT @ID = id FROM INSERTED
DELETE FROM dbo.Table1 WHERE id = @ID
END
GO
4 - Update Test:
UPDATE dbo.Table2
SET dbo.Table2.descricao = t1.descricao
FROM dbo.Table1 t1
WHERE t1.id = dbo.Table2.id
AND t1.id = 2
OBS: By the tests I did I got the following results:
Before the update:
After the Update:
OBS> The article below is very explanatory about triggers. I recommend reading:
https://www.devmedia.com.br/triggers-no-sql-server-teoria-e-pratica-aplicada-em-uma-situacao-real/28194
Give an example of what you want to do along with the definition of the tables involved. Your explanation is a bit confusing.
– anonimo
You will probably be able to do what you want using triggers, but explain your scenario better to make your situation clearer.
– Leticia Rosa