You have two options to do this (in addition to what is already given by @José Diz
):
Here is an example with OUTPUT clause
CREATE TABLE BooksA(
BookID INT,
BookPrice MONEY
);
CREATE TABLE BooksB(
BookID INT,
BookPrice MONEY
);
CREATE TABLE BooksC(
BookID INT,
BookPrice MONEY
);
INSERT INTO BooksA VALUES
(1, 100),
(2, 200),
(3, 100);
INSERT INTO BooksB SELECT * FROM BooksA;
INSERT INTO BooksC SELECT * FROM BooksB;
DECLARE @Price MONEY = 500;
DECLARE @IDs TABLE(ID INT);
BEGIN TRAN
UPDATE BooksA
SET BookPrice = @Price
OUTPUT INSERTED.BookID
INTO @IDs
WHERE BookPrice = 100;
UPDATE BooksB
SET BookPrice = @Price
FROM BooksB BB INNER JOIN @IDs I
ON BB.BookID = I.ID;
UPDATE BooksC
SET BookPrice = @Price
FROM BooksC BC INNER JOIN @IDs I
ON BC.BookID = I.ID;
COMMIT TRAN
SELECT * FROM BooksA;
SELECT * FROM BooksB;
SELECT * FROM BooksC;
Demo
Here is an example with TRIGGERS
:
CREATE TRIGGER Trig1
ON BooksA
AFTER UPDATE
AS
BEGIN
IF UPDATE(BookPrice)
BEGIN
UPDATE BooksB
SET BookPrice = I.BookPrice
FROM BooksB INNER JOIN INSERTED I
ON BooksB.BookID = I.BookID;
--
UPDATE BooksC
SET BookPrice = I.BookPrice
FROM BooksC INNER JOIN INSERTED I
ON BooksC.BookID = I.BookID;
END
END
UPDATE BooksA
SET BookPrice = 500
WHERE BookPrice = 100;
SELECT * FROM BooksA;
SELECT * FROM BooksB;
SELECT * FROM BooksC;
Demo
in sql-server believe to be impossible. But why you have three tables (
livrosA
,livrosB
,livrosC
)? Wouldn’t it be better, for example, to have a tablelivros
and anotherCategoria
?? It may be interesting to review its structure!!– rLinhares
is that I am doing a training at the company where I work and created these tables only for study because my boss asked to research how to do this procedure if possible.
– wilder
@Wilder The change in prices will be book by book or some criterion will be used to change a set of books at once?
– José Diz