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.