SQL SERVER delete record after UPDATE

Asked

Viewed 29 times

1

I would like to know if you can delete the records from the source table after performing the UPDATE in another table, that is, delete the row (from the source table) that was used in the update of another table. I’ve researched enough and I can’t find material , or I don’t know where to look. Thanks!

  • 1

    Give an example of what you want to do along with the definition of the tables involved. Your explanation is a bit confusing.

  • You will probably be able to do what you want using triggers, but explain your scenario better to make your situation clearer.

1 answer

1

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:

Antes Atualização

After the Update:

Despois da Atualização

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

  • Exactly what I needed, Gabriel. Thank you so much!

Browser other questions tagged

You are not signed in. Login or sign up in order to post.