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
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:
Exactly what I needed, Gabriel. Thank you so much!
Browser other questions tagged sql-server sql-update
You are not signed in. Login or sign up in order to post.
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