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

4

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 a way, or what is the best way to: When I give an UPDATE in the Contract table by setting Active = 0, it is done in all tables that have a foreign key in the Contract table.

I'm creating an application using ASP.NET MVC with EntityFramework and DDD.

Thank you

    
asked by anonymous 25.08.2017 / 20:51

2 answers

3

The best solution would be not to replicate the information to the ItemContrato table, after all this would be redundancy. The best way would be to fetch information straight 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 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

The% wc% above searches all foreign keys related to the trigger table along with their columns and dynamically updates the Contrato column according to the value that was entered. Note that Ativo is executed after of each trigger and INSERT .

I also suggest creating a UPDATE for the trigger table that will ensure that when a new record is entered, you already have the ItemContrato column information as 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 additions 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');

Obtaining the following results immediately 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 made the following Ativo :

-- 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 UPDATE are:

╔════╦══════╦═══════╗
║ 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 UPDATE proposed the results before query for the UPDATE table 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 ItemContrato :

╔════╦════════════╦══════╦═══════╗
║ 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, proving that in this case the use of UPDATE to avoid redundancy is the best option.

    
28.08.2017 / 15:58
3

Considering that you have a Context context.

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

In this code I load my Contract entity to be modified and all Contract Item related to it, you can change the expression of FirstOrDefault to your liking.

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.

    
25.08.2017 / 21:20