|
trg_unique_constraints_cols_change_track_insert |
After Insert |
Schema change tracking trigger |
-- =============================================
-- Description: Insert constraint's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_unique_constraints_cols_change_track_insert] ON [unique_constraints_columns]
FOR INSERT
AS
-- skip manual objects created through Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [INSERTED] [i]
JOIN [unique_constraints] [c] ON [c].[unique_constraint_id] = [i].[unique_constraint_id]
WHERE [source] = 'USER'
)
BEGIN
RETURN;
END;
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[ci].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [i].[column_id]
WHERE NOT EXISTS
(
SELECT 1
FROM [schema_updates] [u]
WHERE [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
);
|
Schema change tracking trigger |
-- =============================================
-- Description: Insert constraint's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_unique_constraints_cols_change_track_insert] ON [unique_constraints_columns]
FOR INSERT
AS
-- skip manual objects created through Dataedo application
IF EXISTS
(
SELECT TOP (1) 1
FROM [INSERTED] [i]
JOIN [unique_constraints] [c] ON [c].[unique_constraint_id] = [i].[unique_constraint_id]
WHERE [source] = 'USER'
)
BEGIN
RETURN;
END;
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[ci].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [i].[column_id]
WHERE NOT EXISTS
(
SELECT 1
FROM [schema_updates] [u]
WHERE [u].[update_id] = [i].[update_id]
AND [u].[type] = 'IMPORT'
);
|
|
trg_unique_constraints_cols_change_track_update |
After Update |
Schema change tracking trigger |
-- =============================================
-- Description: Insert constraint's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_unique_constraints_cols_change_track_update] ON [unique_constraints_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 [unique_constraints] [c] ON [c].[unique_constraint_id] = [i].[unique_constraint_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 changes (before and after values) for constraint columns - when constraint was updated
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[before_column_id],
[before_ordinal_position],
[column_name],
[before_column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[d].[column_id],
[d].[ordinal_position],
[ci].[name],
[cd].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [unique_constraints_columns_changes] [c]
WHERE [c].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
AND [c].[valid_to] IS NOT NULL
) [cc]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [i].[column_id]
LEFT JOIN [columns] [cd] ON [cd].[column_id] = [d].[column_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'A'
AND [i].[temp_sync_status] = 0
AND [d].[temp_sync_status] = 1;
BEGIN
-- insert changes (deleted values) for constraint columns- when constraint was deleted
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_to],
[update_id]
)
SELECT [d].[unique_constraint_column_id],
[d].[unique_constraint_id],
[d].[column_id],
[d].[ordinal_position],
[cd].[name],
'DELETED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
LEFT JOIN [columns] [cd] ON [cd].[column_id] = [d].[column_id]
WHERE [i].[status] = 'D'
AND [d].[status] = 'A';
-- insert changes (updated values) for constraint columns- when constraint was restored
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[ci].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [i].[unique_constraint_column_id]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [d].[column_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'D'
AND [i].[temp_sync_status] != 1;
-- check if change for constraint header not exists
IF NOT EXISTS
(
SELECT TOP (1) 1
FROM [unique_constraints_changes] [c]
JOIN [inserted] [i] ON [i].[update_id] = [c].[update_id]
AND [i].[unique_constraint_id] = [c].[unique_constraint_id]
)
AND NOT EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
WHERE [i].[temp_sync_status] = 0
AND [i].[status] = [d].[status]
)
BEGIN
-- insert changes (before and after values) for constraint - when constraint was updated
INSERT INTO [unique_constraints_changes]
([database_id],
[unique_constraint_id],
[table_id],
[name],
[primary_key],
[disabled],
[before_name],
[before_primary_key],
[before_disabled],
[operation],
[valid_from],
[update_id]
)
SELECT TOP 1 [t].[database_id],
[i].[unique_constraint_id],
[c].[table_id],
[c].[name],
[c].[primary_key],
[c].[disabled],
[c].[name],
[c].[primary_key],
[c].[disabled],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
JOIN [unique_constraints] [c] ON [i].[unique_constraint_id] = [c].[unique_constraint_id]
JOIN [tables] [t] ON [t].[table_id] = [c].[table_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [unique_constraints_changes] [c]
WHERE [c].[unique_constraint_id] = [i].[unique_constraint_id]
AND [c].[valid_to] IS NOT NULL
) [cc];
END;
END;
|
Schema change tracking trigger |
-- =============================================
-- Description: Insert constraint's changes to schema change tracking tables
-- =============================================
CREATE TRIGGER [trg_unique_constraints_cols_change_track_update] ON [unique_constraints_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 [unique_constraints] [c] ON [c].[unique_constraint_id] = [i].[unique_constraint_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 changes (before and after values) for constraint columns - when constraint was updated
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[before_column_id],
[before_ordinal_position],
[column_name],
[before_column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[d].[column_id],
[d].[ordinal_position],
[ci].[name],
[cd].[name],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [unique_constraints_columns_changes] [c]
WHERE [c].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
AND [c].[valid_to] IS NOT NULL
) [cc]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [i].[column_id]
LEFT JOIN [columns] [cd] ON [cd].[column_id] = [d].[column_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'A'
AND [i].[temp_sync_status] = 0
AND [d].[temp_sync_status] = 1;
BEGIN
-- insert changes (deleted values) for constraint columns- when constraint was deleted
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_to],
[update_id]
)
SELECT [d].[unique_constraint_column_id],
[d].[unique_constraint_id],
[d].[column_id],
[d].[ordinal_position],
[cd].[name],
'DELETED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
LEFT JOIN [columns] [cd] ON [cd].[column_id] = [d].[column_id]
WHERE [i].[status] = 'D'
AND [d].[status] = 'A';
-- insert changes (updated values) for constraint columns- when constraint was restored
INSERT INTO [unique_constraints_columns_changes]
([unique_constraint_column_id],
[unique_constraint_id],
[column_id],
[ordinal_position],
[column_name],
[operation],
[valid_from],
[update_id]
)
SELECT [i].[unique_constraint_column_id],
[i].[unique_constraint_id],
[i].[column_id],
[i].[ordinal_position],
[ci].[name],
'ADDED',
GETDATE(),
[i].[update_id]
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [i].[unique_constraint_column_id]
LEFT JOIN [columns] [ci] ON [ci].[column_id] = [d].[column_id]
WHERE [i].[status] = 'A'
AND [d].[status] = 'D'
AND [i].[temp_sync_status] != 1;
-- check if change for constraint header not exists
IF NOT EXISTS
(
SELECT TOP (1) 1
FROM [unique_constraints_changes] [c]
JOIN [inserted] [i] ON [i].[update_id] = [c].[update_id]
AND [i].[unique_constraint_id] = [c].[unique_constraint_id]
)
AND NOT EXISTS
(
SELECT TOP (1) 1
FROM [inserted] [i]
INNER JOIN [deleted] [d] ON [i].[unique_constraint_column_id] = [d].[unique_constraint_column_id]
WHERE [i].[temp_sync_status] = 0
AND [i].[status] = [d].[status]
)
BEGIN
-- insert changes (before and after values) for constraint - when constraint was updated
INSERT INTO [unique_constraints_changes]
([database_id],
[unique_constraint_id],
[table_id],
[name],
[primary_key],
[disabled],
[before_name],
[before_primary_key],
[before_disabled],
[operation],
[valid_from],
[update_id]
)
SELECT TOP 1 [t].[database_id],
[i].[unique_constraint_id],
[c].[table_id],
[c].[name],
[c].[primary_key],
[c].[disabled],
[c].[name],
[c].[primary_key],
[c].[disabled],
'UPDATED',
[cc].[valid_to],
[i].[update_id]
FROM [inserted] [i]
JOIN [unique_constraints] [c] ON [i].[unique_constraint_id] = [c].[unique_constraint_id]
JOIN [tables] [t] ON [t].[table_id] = [c].[table_id]
OUTER APPLY
(
SELECT MAX([valid_to]) AS [valid_to]
FROM [unique_constraints_changes] [c]
WHERE [c].[unique_constraint_id] = [i].[unique_constraint_id]
AND [c].[valid_to] IS NOT NULL
) [cc];
END;
END;
|
|
trg_unique_constraints_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_unique_constraints_cols_Modify]
ON [unique_constraints_columns]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE [unique_constraints_columns]
SET last_modification_date = GETDATE(),
modified_by = suser_sname()
WHERE unique_constraint_column_id IN (SELECT DISTINCT unique_constraint_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_unique_constraints_cols_Modify]
ON [unique_constraints_columns]
AFTER INSERT, UPDATE
AS
BEGIN
UPDATE [unique_constraints_columns]
SET last_modification_date = GETDATE(),
modified_by = suser_sname()
WHERE unique_constraint_column_id IN (SELECT DISTINCT unique_constraint_column_id FROM Inserted)
END
|