0

First of all I'm not sure if this website is for purely experts but sorry if it is.. the main page was a little vague

I was wondering if it'd be possible to have a relationship, for example, in my case it would be Customer to saidProduct..

If Customer has a field 'saidProduct' and the table 'saidProduct' has a field 'active'

(I have no knowledge of stored procedures but if this is how it can be done I will figure out how.. that's the only guess I have though)

Can I make it so that if I update Customer.saidProduct = true (was false) so that saidProduct.active = true (was false) as well?

Not sure if it's relevant but I want to simplify my code in VS but I figured asking dba was a better idea

  • 1
    I suppose then if Customer.saidProduct is changed back to false, then the saidProduct.active becomes false again, right? But what if saidProduct.active is updated? Should that affect some or all rows with that Customer.saidProduct? – ypercubeᵀᴹ Aug 12 '14 at 16:37
  • Seems like what you want is a link table "CustomerSaidProduct". The fields in the table would be CustomerID/SaidProductID. The idea is that if a row exists for that Customer, for that product, in the table, it represents an Active saidProduct. If there are no fields for that customer, there are no active saidProducts. Sound like what you are after? – blobbles Aug 12 '14 at 16:48
  • Just cleared it up.. saidProduct would never be changed on its own.. the only time it would be changed is if it had been changed in Customer.saidProduct - @Blobbles I think I know what you mean, apologies for not entirely understanding as I have never used bridges except for in my db class.. I think that might work except the entries stay in the product table even if they are inactive. So the ID would always be there, even if inactive. – brianforan Aug 12 '14 at 16:48
  • @pbrianq Why do you think any entry needs to go/stay in the Product table? – Aaron Bertrand Aug 12 '14 at 17:24
  • @Aaron Bertrand because I made the table – brianforan Aug 12 '14 at 17:31
1

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.

| improve this answer | |
  • 1
    Not sure I quite agree with your first sentence. Triggers are like any other feature of SQL Server: they can be used effectively, and they can be abused. I've implemented plenty of triggers in my day when other methods were impractical, and the fear of poor performance was never the primary reason for a different implementation when triggers were a valid choice. – Aaron Bertrand Aug 12 '14 at 17:23
  • I can see where performance in this case might actually be better using a trigger as opposed to having the client update both tables in separate statements. I'll reword my answer. – Max Vernon Aug 12 '14 at 17:30
  • Sorry if this is a dumb question but why would I want to drop the two tables at the start? I decided to just run an if exists.. then update the value in Product.active, and after the original update I'm adding IF NOT EXISTS (...) to then insert that Customer ID with null fields otherwise into the Product table – brianforan Aug 12 '14 at 17:34
  • Ooooops. I used that while I was constructing my answer. I'll edit my answer. Luckily all that would take place in Tempdb since I had USE tempdb; at the beginning. – Max Vernon Aug 12 '14 at 17:35

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.