How to update all records of all tables that have FK of a table

Question

I have the following tables:

CREATE TABLE Contrato (
  [Id [int] NOT NULL IDENTITY,
  [Nome] [varchar](150),
  [Ativo] [bit]
  PRIMARY KEY ([Id])
)
CREATE TABLE [dbo].[ItemContrato] (
  [Id] [int] NOT NULL IDENTITY,
  [ContratoId] [int],
  [Nome] [varchar](150),
  [Ativo] [bit]
  PRIMARY KEY ([Id])
)
 ALTER TABLE [dbo].[ItemContrato] ADD FOREIGN KEY ([ContratoId]) REFERENCES [dbo].[Contrato] ([Id]);

With the following records:

 Contrato
 Id | Nome      | Ativo
 1  |Contrato A |  1
 2  |Contrato B |  1

 ItemContrato
 Id | ContratoId | Nome                | Ativo
 1  |     1      |Item de Contrato A1  |  1
 2  |     1      |Item de Contrato A2  |  1
 3  |     2      |Item de Contrato B1  |  1

I want to know if there is how, or what is the best way to: When I give an UPDATE in the table Active Contract = 0, the same is done in all tables that have a foreign key of the table Contract.

I am creating an application using ASP.NET MVC with Entityframework and DDD.

Thank you

Answer 1

The best solution would be not to replicate the information to the table ItemContrato, after all this would be redundancy. The best way would be to fetch the information directly from the source table:

SELECT ic.id,
       ic.contratoid,
       ic.nome,
       c.ativo
  FROM ItemContrato ic WITH(NOLOCK)
       INNER JOIN Contrato c WITH(NOLOCK) ON c.id = ic.contratoid;

---

But if you really want to replicate the information, I suggest using a trigger to make the change:

IF OBJECT_ID('tgr_contrato_aiu', 'TR') IS NULL
BEGIN
  EXEC('CREATE TRIGGER tgr_contrato_aiu ON Contrato FOR INSERT, UPDATE AS BEGIN SELECT 1 END');
END;
GO

ALTER TRIGGER tgr_contrato_aiu
ON Contrato
FOR INSERT, UPDATE
AS
BEGIN
  DECLARE @nome_tabela VARCHAR(100),
          @query       VARCHAR(MAX);

  SET NOCOUNT ON;

  -- Pega o nome da tabela para a qual a TRIGGER é executada
  SELECT @nome_tabela = OBJECT_NAME(o.parent_object_id)
    FROM sys.objects o WITH(NOLOCK)
   WHERE o.name = OBJECT_NAME(@@PROCID);

  SELECT @query = isnull(@query + CHAR(10), '') + 'UPDATE f' + CHAR(10) +
                                                  '   SET f.ativo = ' +  + CAST(i.ativo AS VARCHAR) + CHAR(10) +
                                                  '  FROM ' + tf.name + ' f' + CHAR(10) +
                                                  ' WHERE f.' + c.name + ' = ' + CAST(i.id AS VARCHAR) + ';' + CHAR(10)
    FROM sys.tables t WITH(NOLOCK)
         INNER JOIN sys.foreign_keys fk WITH(NOLOCK) ON fk.referenced_object_id = t.object_id
         INNER JOIN sys.tables tf WITH(NOLOCK) ON tf.object_id = fk.parent_object_id
         INNER JOIN sys.foreign_key_columns fkc WITH(NOLOCK) ON fkc.constraint_object_id = fk.object_id
         INNER JOIN sys.columns c WITH(NOLOCK) ON c.object_id = fkc.parent_object_id
                                              AND c.column_id = fkc.parent_column_id
         CROSS JOIN inserted i
         INNER JOIN deleted d ON d.id = i.id
   WHERE t.name = @nome_tabela
     -- Somente se mudar algo na coluna "Ativo"
     AND i.ativo <> d.ativo
     -- Garante que exista a coluna "Ativo" na tabela filha
     AND EXISTS(SELECT 1
                  FROM sys.columns cf WITH(NOLOCK)
                 WHERE cf.object_id = fkc.parent_object_id
                   AND cf.name = 'ATIVO');

  IF @query IS NOT NULL
  BEGIN
    -- PRINT @query;
    EXEC(@query);
  END;
END;
GO

To trigger above searches all foreign keys related to table Contrato, together with your columns and dynamically updates the column Ativo according to the value that was entered. Note that the trigger is executed afterward of each INSERT and UPDATE.

I also suggest the creation of a trigger to the table ItemContrato which will ensure that when a new record is entered, it is already with column information Ativo in the way that is expected:

IF OBJECT_ID('tgr_itemcontrato_ai', 'TR') IS NULL
BEGIN
  EXEC('CREATE TRIGGER tgr_itemcontrato_ai ON ItemContrato FOR INSERT AS BEGIN SELECT 1 END');
END;
GO

ALTER TRIGGER tgr_itemcontrato_ai
ON ItemContrato
FOR INSERT
AS
BEGIN
  SET NOCOUNT ON;

  UPDATE ic
     SET ic.ativo = c.ativo
    FROM ItemContrato ic
         INNER JOIN inserted i ON i.id = ic.id
         INNER JOIN Contrato c ON c.id = ic.contratoid;
END;
GO

---

For testing I used the following inclusions in the tables:

SET NOCOUNT ON;

DECLARE @ultimo_codigo INT;

-- Inserção dos dados para teste
-- Contrato "X"
INSERT INTO Contrato(nome, ativo)
              VALUES('X', 1);
SET @ultimo_codigo = SCOPE_IDENTITY();

INSERT INTO ItemContrato(contratoid, nome)
                  VALUES(@ultimo_codigo, 'A'),
                        (@ultimo_codigo, 'B'),
                        (@ultimo_codigo, 'C');

-- Contrato "Y"
INSERT INTO Contrato(nome, ativo)
              VALUES('Y', 1);
SET @ultimo_codigo = SCOPE_IDENTITY();

INSERT INTO ItemContrato(contratoid, nome)
                  VALUES(@ultimo_codigo, 'D'),
                        (@ultimo_codigo, 'E'),
                        (@ultimo_codigo, 'F'),
                        (@ultimo_codigo, 'G');

-- Contrato "Z"
INSERT INTO Contrato(nome, ativo)
              VALUES('Z', 0);
SET @ultimo_codigo = SCOPE_IDENTITY();

INSERT INTO ItemContrato(contratoid, nome)
                  VALUES(@ultimo_codigo, 'H'),
                        (@ultimo_codigo, 'I'),
                        (@ultimo_codigo, 'J');

Getting the results right after insertion:

╔════╦══════╦═══════╗
║ Id ║ Nome ║ Ativo ║
╠════╬══════╬═══════╣
║ 1  ║ X    ║ 1     ║
║ 2  ║ Y    ║ 1     ║
║ 3  ║ Z    ║ 0     ║
╚════╩══════╩═══════╝

╔════╦════════════╦══════╦═══════╗
║ Id ║ ContratoId ║ Nome ║ Ativo ║
╠════╬════════════╬══════╬═══════╣
║ 1  ║ 1          ║ A    ║ 1     ║
║ 2  ║ 1          ║ B    ║ 1     ║
║ 3  ║ 1          ║ C    ║ 1     ║
║ 4  ║ 2          ║ D    ║ 1     ║
║ 5  ║ 2          ║ E    ║ 1     ║
║ 6  ║ 2          ║ F    ║ 1     ║
║ 7  ║ 2          ║ G    ║ 1     ║
║ 8  ║ 3          ║ H    ║ 0     ║
║ 9  ║ 3          ║ I    ║ 0     ║
║10  ║ 3          ║ J    ║ 0     ║
╚════╩════════════╩══════╩═══════╝

Soon after I performed the following UPDATE:

-- Atualização dos contratos "X" e "Z"
UPDATE c
   SET c.ativo = 0
  FROM Contrato c
 WHERE c.nome = 'X';

UPDATE c
   SET c.ativo = 1
  FROM Contrato c
 WHERE c.nome = 'Z';

And the records after the UPDATE sane:

╔════╦══════╦═══════╗
║ Id ║ Nome ║ Ativo ║
╠════╬══════╬═══════╣
║ 1  ║ X    ║ 0     ║
║ 2  ║ Y    ║ 1     ║
║ 3  ║ Z    ║ 1     ║
╚════╩══════╩═══════╝

╔════╦════════════╦══════╦═══════╗
║ Id ║ ContratoId ║ Nome ║ Ativo ║
╠════╬════════════╬══════╬═══════╣
║ 1  ║ 1          ║ A    ║ 0     ║
║ 2  ║ 1          ║ B    ║ 0     ║
║ 3  ║ 1          ║ C    ║ 0     ║
║ 4  ║ 2          ║ D    ║ 1     ║
║ 5  ║ 2          ║ E    ║ 1     ║
║ 6  ║ 2          ║ F    ║ 1     ║
║ 7  ║ 2          ║ G    ║ 1     ║
║ 8  ║ 3          ║ H    ║ 1     ║
║ 9  ║ 3          ║ I    ║ 1     ║
║10  ║ 3          ║ J    ║ 1     ║
╚════╩════════════╩══════╩═══════╝

In the case of the first query the results before the UPDATE to the table ItemContrato were:

╔════╦════════════╦══════╦═══════╗
║ Id ║ ContratoId ║ Nome ║ Ativo ║
╠════╬════════════╬══════╬═══════╣
║ 1  ║ 1          ║ A    ║ 1     ║
║ 2  ║ 1          ║ B    ║ 1     ║
║ 3  ║ 1          ║ C    ║ 1     ║
║ 4  ║ 2          ║ D    ║ 1     ║
║ 5  ║ 2          ║ E    ║ 1     ║
║ 6  ║ 2          ║ F    ║ 1     ║
║ 7  ║ 2          ║ G    ║ 1     ║
║ 8  ║ 3          ║ H    ║ 0     ║
║ 9  ║ 3          ║ I    ║ 0     ║
║10  ║ 3          ║ J    ║ 0     ║
╚════╩════════════╩══════╩═══════╝

And after the UPDATE:

╔════╦════════════╦══════╦═══════╗
║ Id ║ ContratoId ║ Nome ║ Ativo ║
╠════╬════════════╬══════╬═══════╣
║ 1  ║ 1          ║ A    ║ 0     ║
║ 2  ║ 1          ║ B    ║ 0     ║
║ 3  ║ 1          ║ C    ║ 0     ║
║ 4  ║ 2          ║ D    ║ 1     ║
║ 5  ║ 2          ║ E    ║ 1     ║
║ 6  ║ 2          ║ F    ║ 1     ║
║ 7  ║ 2          ║ G    ║ 1     ║
║ 8  ║ 3          ║ H    ║ 1     ║
║ 9  ║ 3          ║ I    ║ 1     ║
║10  ║ 3          ║ J    ║ 1     ║
╚════╩════════════╩══════╩═══════╝

Note that the results are the same, thus proving that for this case the use of JOIN to avoid redundancy is the best option.

Answer 2

Taking into account that you have a context Context.

var result = Context.Contrato.Include(rel => rel.ItemContrato).FirstOrDefault(prop => prop.Id == id);

In this code I charge my Contract entity to be modified and all Itemcontrate related to it, you can change the expression of the FirstOrDefault according to your taste.

Context.Entry(result).State = EntityState.Modified;
result.Ativo = 0;
foreach (var itemContrato in result.ItemContratos)
{
    itemContrato.Ativo = 0;
    Context.Entry(itemContrato).State = EntityState.Modified;
}
Context.SaveChanges();

It may not be the best way to do it, but it will solve your problem.