You could create a trigger on both tables to update the other table when changes are made to the data.
I've create a sample schema to test this, and to show results:
USE tempdb;
GO
CREATE TABLE dbo.Products
(
ProductID INT NOT NULL CONSTRAINT PK_Products PRIMARY KEY CLUSTERED IDENTITY(1,1)
, Active BIT NOT NULL CONSTRAINT DF_Products_Active DEFAULT ((0))
);
GO
INSERT INTO dbo.Products(Active)
VALUES (0);
GO
CREATE TABLE dbo.Customers
(
CustomerID INT NOT NULL CONSTRAINT PK_Customers PRIMARY KEY CLUSTERED IDENTITY(1,1)
, ProductID INT NOT NULL CONSTRAINT FK_Customers_ProductID FOREIGN KEY REFERENCES dbo.Products(ProductID)
, SaidProduct BIT NOT NULL CONSTRAINT DF_Customers_SaidProduct DEFAULT ((0))
);
GO
CREATE TRIGGER dbo.CustomerTrigger ON dbo.Customers
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE dbo.Products
SET Active = SaidProduct
FROM dbo.Products p
INNER JOIN inserted i ON i.ProductID = p.ProductID;
END
GO
INSERT INTO dbo.Customers(ProductID, SaidProduct)
VALUES (1, 1);
GO
SELECT *
FROM dbo.Products;
This inserts a row into the Products table. The product is inserted with an Active status of False. A row is then inserted into the Customers table, where SaidProduct is set to True, which triggers an update on the Products table. The result is the row in the Products table is now set to Active = True.
Trigger recursion may be an issue if you create a trigger on both tables, such that updating the Customers table would update the Products table, which might then try to update the Customers table, thereby creating a loop. There are several ways to avoid recursion, but that is slightly beyond the scope of your question.
Customer.saidProductis changed back tofalse, then thesaidProduct.activebecomesfalseagain, right? But what ifsaidProduct.activeis updated? Should that affect some or all rows with thatCustomer.saidProduct?