|
trg_tables_relations_cols_change_track_insert |
After Insert |
Schema change tracking trigger |
-- =============================================
-- Description: Insert relation column's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_change_track_insert] ON [tables_relations_columns]
FOR INSERT
AS
-- skip manual objects created through Dataedo application
-- or first documentation import to Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [tables_relations] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
WHERE [source] = 'USER'
)
OR EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
)
BEGIN
RETURN;
END;
INSERT INTO [tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[ci_fk].[name],
[ci_pk].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
LEFT JOIN [columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id];
|
Schema change tracking trigger |
-- =============================================
-- Description: Insert relation column's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_change_track_insert] ON [tables_relations_columns]
FOR INSERT
AS
-- skip manual objects created through Dataedo application
-- or first documentation import to Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [tables_relations] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
WHERE [source] = 'USER'
)
OR EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [schema_updates] [u] ON [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
)
BEGIN
RETURN;
END;
INSERT INTO [tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[ci_fk].[name],
[ci_pk].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
LEFT JOIN [columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id];
|
|
trg_tables_relations_cols_change_track_update |
After Update |
Schema change tracking trigger |
-- =============================================
-- Description: Insert relation colum's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_change_track_update] ON [tables_relations_columns]
FOR UPDATE
AS
-- skip manual objects created through Dataedo application
-- or first documentation import to Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [dbo].[tables_relations] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
WHERE [source] = 'USER'
)
OR EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [dbo].[schema_updates] [u] ON [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
)
BEGIN
RETURN;
END;
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
WHERE ISNULL([d].[column_fk_id],'') <> [i].[column_fk_id]
OR ISNULL([d].[column_pk_id],'') <> [i].[column_pk_id]
OR ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position]
)
BEGIN
-- insert changes (before and after values) for relation columns - when column was updated
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[before_column_fk_id],
[before_column_pk_id],
[before_ordinal_position],
[column_fk_name],
[before_column_fk_name],
[column_pk_name],
[before_column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[d].[column_fk_id],
[d].[column_pk_id],
[d].[ordinal_position],
[ci_fk].[name],
[cd_fk].[name],
[ci_pk].[name],
[cd_pk].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [cd_fk] ON [cd_fk].[column_id] = [d].[column_fk_id]
LEFT JOIN [dbo].[columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [dbo].[columns] [cd_pk] ON [cd_pk].[column_id] = [d].[column_pk_id]
LEFT JOIN [dbo].[columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [dbo].[tables_relations_columns_changes] [c]
WHERE [c].[table_relation_column_id] = [d].[table_relation_column_id]
AND [c].[valid_to] IS NOT NULL
) [cc]
WHERE [i].[status] = 'A'
AND [d].[status] = 'A'
AND [i].[temp_sync_status] = 0
AND [d].[temp_sync_status] = 1;
END;
BEGIN
-- insert changes (deleted values) for relation columns - when column was deleted
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_to],
[update_id]
)
SELECT [d].[table_relation_column_id],
[d].[table_relation_id],
[d].[column_fk_id],
[d].[column_pk_id],
[d].[ordinal_position],
[cd_fk].[name],
[cd_pk].[name],
'DELETED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [cd_fk] ON [cd_fk].[column_id] = [d].[column_fk_id]
LEFT JOIN [dbo].[columns] [cd_pk] ON [cd_pk].[column_id] = [d].[column_pk_id]
WHERE [i].[status] = 'D'
AND [d].[status] = 'A';
-- insert changes (updated values) for relation columns - when column was restored
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[ci_fk].[name],
[ci_pk].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [dbo].[columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'D'
AND [i].[temp_sync_status] != 1;
-- check if change for relation header not exists
IF NOT EXISTS
(
SELECT TOP (1) 1
FROM [dbo].[tables_relations_changes] [c]
JOIN [inserted] [i] ON [i].[update_id] = [c].[update_id]
AND [i].[table_relation_id] = [c].[table_relation_id]
)
AND NOT EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
WHERE [i].[temp_sync_status] = 0
AND [i].[status] = [d].[status]
)
BEGIN
-- insert changes (before and after values) for relation - when relation was updated
INSERT INTO [dbo].[tables_relations_changes]
([database_id],
[table_relation_id],
[pk_table_id],
[fk_table_id],
[name],
[update_rule],
[delete_rule],
[disabled],
[fk_type],
[pk_type],
[before_name],
[before_update_rule],
[before_delete_rule],
[before_disabled],
[before_fk_type],
[before_pk_type],
[pk_table_schema],
[pk_table_name],
[fk_table_schema],
[fk_table_name],
[before_pk_table_schema],
[before_pk_table_name],
[before_fk_table_schema],
[before_fk_table_name],
[operation],
[valid_from],
[update_id]
)
SELECT TOP 1 [tb].[database_id],
[i].[table_relation_id],
[c].[pk_table_id],
[c].[fk_table_id],
[c].[name],
[c].[update_rule],
[c].[delete_rule],
[c].[disabled],
[c].[fk_type],
[c].[pk_type],
[c].[name],
[c].[update_rule],
[c].[delete_rule],
[c].[disabled],
[c].[fk_type],
[c].[pk_type],
[tb_pk].[schema],
[tb_pk].[name],
[tb].[schema],
[tb].[name],
[tb_pk].[schema],
[tb_pk].[name],
[tb].[schema],
[tb].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
JOIN [dbo].[tables_relations_changes] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
JOIN [dbo].[tables] [tb] ON [tb].[table_id] = [c].[fk_table_id]
LEFT JOIN [dbo].[tables] [tb_pk] ON [tb_pk].[table_id] = [c].[pk_table_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [dbo].[tables_relations_changes] [c]
WHERE [c].[table_relation_id] = [i].[table_relation_id]
AND [c].[valid_to] IS NOT NULL
) [cc];
END;
END;
|
Schema change tracking trigger |
-- =============================================
-- Description: Insert relation colum's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_change_track_update] ON [tables_relations_columns]
FOR UPDATE
AS
-- skip manual objects created through Dataedo application
-- or first documentation import to Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [dbo].[tables_relations] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
WHERE [source] = 'USER'
)
OR EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
JOIN [dbo].[schema_updates] [u] ON [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
)
BEGIN
RETURN;
END;
IF EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
WHERE ISNULL([d].[column_fk_id],'') <> [i].[column_fk_id]
OR ISNULL([d].[column_pk_id],'') <> [i].[column_pk_id]
OR ISNULL([d].[ordinal_position],'') <> [i].[ordinal_position]
)
BEGIN
-- insert changes (before and after values) for relation columns - when column was updated
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[before_column_fk_id],
[before_column_pk_id],
[before_ordinal_position],
[column_fk_name],
[before_column_fk_name],
[column_pk_name],
[before_column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[d].[column_fk_id],
[d].[column_pk_id],
[d].[ordinal_position],
[ci_fk].[name],
[cd_fk].[name],
[ci_pk].[name],
[cd_pk].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [cd_fk] ON [cd_fk].[column_id] = [d].[column_fk_id]
LEFT JOIN [dbo].[columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [dbo].[columns] [cd_pk] ON [cd_pk].[column_id] = [d].[column_pk_id]
LEFT JOIN [dbo].[columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [dbo].[tables_relations_columns_changes] [c]
WHERE [c].[table_relation_column_id] = [d].[table_relation_column_id]
AND [c].[valid_to] IS NOT NULL
) [cc]
WHERE [i].[status] = 'A'
AND [d].[status] = 'A'
AND [i].[temp_sync_status] = 0
AND [d].[temp_sync_status] = 1;
END;
BEGIN
-- insert changes (deleted values) for relation columns - when column was deleted
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_to],
[update_id]
)
SELECT [d].[table_relation_column_id],
[d].[table_relation_id],
[d].[column_fk_id],
[d].[column_pk_id],
[d].[ordinal_position],
[cd_fk].[name],
[cd_pk].[name],
'DELETED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [cd_fk] ON [cd_fk].[column_id] = [d].[column_fk_id]
LEFT JOIN [dbo].[columns] [cd_pk] ON [cd_pk].[column_id] = [d].[column_pk_id]
WHERE [i].[status] = 'D'
AND [d].[status] = 'A';
-- insert changes (updated values) for relation columns - when column was restored
INSERT INTO [dbo].[tables_relations_columns_changes]
([table_relation_column_id],
[table_relation_id],
[column_fk_id],
[column_pk_id],
[ordinal_position],
[column_fk_name],
[column_pk_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[table_relation_column_id],
[i].[table_relation_id],
[i].[column_fk_id],
[i].[column_pk_id],
[i].[ordinal_position],
[ci_fk].[name],
[ci_pk].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
LEFT JOIN [dbo].[columns] [ci_fk] ON [ci_fk].[column_id] = [i].[column_fk_id]
LEFT JOIN [dbo].[columns] [ci_pk] ON [ci_pk].[column_id] = [i].[column_pk_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'D'
AND [i].[temp_sync_status] != 1;
-- check if change for relation header not exists
IF NOT EXISTS
(
SELECT TOP (1) 1
FROM [dbo].[tables_relations_changes] [c]
JOIN [inserted] [i] ON [i].[update_id] = [c].[update_id]
AND [i].[table_relation_id] = [c].[table_relation_id]
)
AND NOT EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[table_relation_column_id] = [d].[table_relation_column_id]
WHERE [i].[temp_sync_status] = 0
AND [i].[status] = [d].[status]
)
BEGIN
-- insert changes (before and after values) for relation - when relation was updated
INSERT INTO [dbo].[tables_relations_changes]
([database_id],
[table_relation_id],
[pk_table_id],
[fk_table_id],
[name],
[update_rule],
[delete_rule],
[disabled],
[fk_type],
[pk_type],
[before_name],
[before_update_rule],
[before_delete_rule],
[before_disabled],
[before_fk_type],
[before_pk_type],
[pk_table_schema],
[pk_table_name],
[fk_table_schema],
[fk_table_name],
[before_pk_table_schema],
[before_pk_table_name],
[before_fk_table_schema],
[before_fk_table_name],
[operation],
[valid_from],
[update_id]
)
SELECT TOP 1 [tb].[database_id],
[i].[table_relation_id],
[c].[pk_table_id],
[c].[fk_table_id],
[c].[name],
[c].[update_rule],
[c].[delete_rule],
[c].[disabled],
[c].[fk_type],
[c].[pk_type],
[c].[name],
[c].[update_rule],
[c].[delete_rule],
[c].[disabled],
[c].[fk_type],
[c].[pk_type],
[tb_pk].[schema],
[tb_pk].[name],
[tb].[schema],
[tb].[name],
[tb_pk].[schema],
[tb_pk].[name],
[tb].[schema],
[tb].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
JOIN [dbo].[tables_relations_changes] [c] ON [c].[table_relation_id] = [i].[table_relation_id]
JOIN [dbo].[tables] [tb] ON [tb].[table_id] = [c].[fk_table_id]
LEFT JOIN [dbo].[tables] [tb_pk] ON [tb_pk].[table_id] = [c].[pk_table_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [dbo].[tables_relations_changes] [c]
WHERE [c].[table_relation_id] = [i].[table_relation_id]
AND [c].[valid_to] IS NOT NULL
) [cc];
END;
END;
|
|
trg_tables_relations_cols_Modify |
After Insert, Update |
Standard insert/update trigger setting last_modification_date, modified by columns |
-- =============================================
-- Author: Piotr Kononow
-- Create date: 2014-06-18
-- Description: Updates last_modification_date, modified_by columns on insert or update
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_Modify]
ON [tables_relations_columns]
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE [tables_relations_columns]
SET last_modification_date = GETDATE(),
modified_by = suser_sname()
WHERE table_relation_column_id IN (SELECT DISTINCT table_relation_column_id FROM Inserted)
END
|
Standard insert/update trigger setting last_modification_date, modified by columns |
-- =============================================
-- Author: Piotr Kononow
-- Create date: 2014-06-18
-- Description: Updates last_modification_date, modified_by columns on insert or update
-- =============================================
CREATE TRIGGER [trg_tables_relations_cols_Modify]
ON [tables_relations_columns]
AFTER INSERT,UPDATE
AS
BEGIN
UPDATE [tables_relations_columns]
SET last_modification_date = GETDATE(),
modified_by = suser_sname()
WHERE table_relation_column_id IN (SELECT DISTINCT table_relation_column_id FROM Inserted)
END
|