dbo.tables_relations (Table relationships)

Dataedo repository 7

- Dataedo repository 7
  - Modules
  - Tables

Documentation	Dataedo repository 7
Schema	dbo
Name	tables_relations
Title	Table relationships
Module	Schema and Metadata
Type	Schema
Status	Active

Holds table relationships (foreign keys), those defined in DBMS and those defined by user in Dataedo. The two types are distinguished by source column (DBMS/USER).

Columns

		Name	Data type	Attributes	References	Description	Type	Status
	1	table_relation_id	int	Identity		Row id	Metadata	Active
Row id Identity Type: Metadata Status: Active
	2	pk_table_id	int		dbo.tables (Tables and Views)	Primary key table	Metadata	Active
Primary key table References: dbo.tables (Tables and Views) Type: Metadata Status: Active
	3	fk_table_id	int		dbo.tables (Tables and Views)	Foreign key table	Metadata	Active
Foreign key table References: dbo.tables (Tables and Views) Type: Metadata Status: Active
	4	name	nvarchar(250)			Relation name from DBMS or assigned by user if relation is user defined	Schema	Active
Relation name from DBMS or assigned by user if relation is user defined Type: Schema Status: Active
	5	source	nvarchar(50)	Default: 'USER'		Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository	Metadata	Active
Source of the row: 'DBMS' for imported from database, 'USER' for manual defined by user in Dataedo repository Default: 'USER' Type: Metadata Status: Active
	6	status	char(1)	Default: 'A'		'D' if relation was deleted from DBMS, 'A' otherwise	Metadata	Active
'D' if relation was deleted from DBMS, 'A' otherwise Default: 'A' Type: Metadata Status: Active
	7	title	nvarchar(250)			Relationship title	Documentation	Active
Relationship title Type: Documentation Status: Active
	8	description	nvarchar(MAX)			Relation description defined with Dataedo.	Documentation	Active
Relation description defined with Dataedo. Type: Documentation Status: Active
	9	fk_type	nvarchar(15)	Default: N'MANY'		Foreign key table cardinality: MANY (default), ONE	Documentation	Active
Foreign key table cardinality: MANY (default), ONE Default: N'MANY' Type: Documentation Status: Active
	10	pk_type	nvarchar(15)	Default: N'ONE'		Promary key table cardinality: Currently available only ONE	Documentation	Active
Promary key table cardinality: Currently available only ONE Default: N'ONE' Type: Documentation Status: Active
	11	disabled	bit			Is imported foreign key disabled in DBMS (1 - yes)	Schema	Active
Is imported foreign key disabled in DBMS (1 - yes) Type: Schema Status: Active
	12	update_rule	nvarchar(100)			Update rule defined in DBMS: NO ACTION, SET NULL, CASCADE	Schema	Active
Update rule defined in DBMS: NO ACTION, SET NULL, CASCADE Type: Schema Status: Active
	13	delete_rule	nvarchar(100)			Delete rule defined in DBMS: NO ACTION, SET NULL, CASCADE	Schema	Active
Delete rule defined in DBMS: NO ACTION, SET NULL, CASCADE Type: Schema Status: Active
	14	field1	nvarchar(MAX)			Custom field 1	Documentation	Active
Custom field 1 Type: Documentation Status: Active
	15	field2	nvarchar(MAX)			Custom field 2	Documentation	Active
Custom field 2 Type: Documentation Status: Active
	16	field3	nvarchar(MAX)			Custom field 3	Documentation	Active
Custom field 3 Type: Documentation Status: Active
	17	field4	nvarchar(MAX)			Custom field 4	Documentation	Active
Custom field 4 Type: Documentation Status: Active
	18	field5	nvarchar(MAX)			Custom field 5	Documentation	Active
Custom field 5 Type: Documentation Status: Active
	19	field6	nvarchar(MAX)			Custom field 6	Documentation	Active
Custom field 6 Type: Documentation Status: Active
	20	field7	nvarchar(MAX)			Custom field 7	Documentation	Active
Custom field 7 Type: Documentation Status: Active
	21	field8	nvarchar(MAX)			Custom field 8	Documentation	Active
Custom field 8 Type: Documentation Status: Active
	22	field9	nvarchar(MAX)			Custom field 9	Documentation	Active
Custom field 9 Type: Documentation Status: Active
	23	field10	nvarchar(MAX)			Custom field 10	Documentation	Active
Custom field 10 Type: Documentation Status: Active
	24	field11	nvarchar(MAX)			Custom field 11	Documentation	Active
Custom field 11 Type: Documentation Status: Active
	25	field12	nvarchar(MAX)			Custom field 12	Documentation	Active
Custom field 12 Type: Documentation Status: Active
	26	field13	nvarchar(MAX)			Custom field 13	Documentation	Active
Custom field 13 Type: Documentation Status: Active
	27	field14	nvarchar(MAX)			Custom field 14	Documentation	Active
Custom field 14 Type: Documentation Status: Active
	28	field15	nvarchar(MAX)			Custom field 15	Documentation	Active
Custom field 15 Type: Documentation Status: Active
	29	field16	nvarchar(MAX)			Custom field 16	Documentation	Active
Custom field 16 Type: Documentation Status: Active
	30	field17	nvarchar(MAX)			Custom field 17	Documentation	Active
Custom field 17 Type: Documentation Status: Active
	31	field18	nvarchar(MAX)			Custom field 18	Documentation	Active
Custom field 18 Type: Documentation Status: Active
	32	field19	nvarchar(MAX)			Custom field 19	Documentation	Active
Custom field 19 Type: Documentation Status: Active
	33	field20	nvarchar(MAX)			Custom field 20	Documentation	Active
Custom field 20 Type: Documentation Status: Active
	34	field21	nvarchar(MAX)			Custom field 21	Documentation	Active
Custom field 21 Type: Documentation Status: Active
	35	field22	nvarchar(MAX)			Custom field 22	Documentation	Active
Custom field 22 Type: Documentation Status: Active
	36	field23	nvarchar(MAX)			Custom field 23	Documentation	Active
Custom field 23 Type: Documentation Status: Active
	37	field24	nvarchar(MAX)			Custom field 24	Documentation	Active
Custom field 24 Type: Documentation Status: Active
	38	field25	nvarchar(MAX)			Custom field 25	Documentation	Active
Custom field 25 Type: Documentation Status: Active
	39	field26	nvarchar(MAX)			Custom field 26	Documentation	Active
Custom field 26 Type: Documentation Status: Active
	40	field27	nvarchar(MAX)			Custom field 27	Documentation	Active
Custom field 27 Type: Documentation Status: Active
	41	field28	nvarchar(MAX)			Custom field 28	Documentation	Active
Custom field 28 Type: Documentation Status: Active
	42	field29	nvarchar(MAX)			Custom field 29	Documentation	Active
Custom field 29 Type: Documentation Status: Active
	43	field30	nvarchar(MAX)			Custom field 30	Documentation	Active
Custom field 30 Type: Documentation Status: Active
	44	field31	nvarchar(MAX)			Custom field 31	Documentation	Active
Custom field 31 Type: Documentation Status: Active
	45	field32	nvarchar(MAX)			Custom field 32	Documentation	Active
Custom field 32 Type: Documentation Status: Active
	46	field33	nvarchar(MAX)			Custom field 33	Documentation	Active
Custom field 33 Type: Documentation Status: Active
	47	field34	nvarchar(MAX)			Custom field 34	Documentation	Active
Custom field 34 Type: Documentation Status: Active
	48	field35	nvarchar(MAX)			Custom field 35	Documentation	Active
Custom field 35 Type: Documentation Status: Active
	49	field36	nvarchar(MAX)			Custom field 36	Documentation	Active
Custom field 36 Type: Documentation Status: Active
	50	field37	nvarchar(MAX)			Custom field 37	Documentation	Active
Custom field 37 Type: Documentation Status: Active
	51	field38	nvarchar(MAX)			Custom field 38	Documentation	Active
Custom field 38 Type: Documentation Status: Active
	52	field39	nvarchar(MAX)			Custom field 39	Documentation	Active
Custom field 39 Type: Documentation Status: Active
	53	field40	nvarchar(MAX)			Custom field 40	Documentation	Active
Custom field 40 Type: Documentation Status: Active
	54	creation_date	datetime	Default: getdate()		Standard row creation timestamp	Metadata	Active
Standard row creation timestamp Default: getdate() Type: Metadata Status: Active
	55	created_by	nvarchar(1024)	Default: user_name()		Dataedo username that created row	Metadata	Active
Dataedo username that created row Default: user_name() Type: Metadata Status: Active
	56	last_modification_date	datetime	Default: getdate()		Standard row last update timestamp	Metadata	Active
Standard row last update timestamp Default: getdate() Type: Metadata Status: Active
	57	modified_by	nvarchar(1024)	Default: user_name()		Dataedo username that last updated row	Metadata	Active
Dataedo username that last updated row Default: user_name() Type: Metadata Status: Active
	58	source_id	int			For internal use only	Internal	Active
For internal use only Type: Internal Status: Active
	59	temp_sync_status	bit	Default: 0		For internal use only	Internal	Active
For internal use only Default: 0 Type: Internal Status: Active
	60	update_id	int		dbo.schema_updates (Imports/Updates)	Id of the last schema import/update from source database where element was created/modified	Metadata	Active
Id of the last schema import/update from source database where element was created/modified References: dbo.schema_updates (Imports/Updates) Type: Metadata Status: Active

Relations

Foreign table	Primary table	Join	Title / Name / Description
dbo.tables_relations (Table relationships)	dbo.schema_updates (Imports/Updates)	dbo.tables_relations.update_id = dbo.schema_updates.update_id	fk_tables_relations_schema_updates
dbo.tables_relations.update_id = dbo.schema_updates.update_id Name: fk_tables_relations_schema_updates
dbo.tables_relations (Table relationships)	dbo.tables (Tables and Views)	dbo.tables_relations.fk_table_id = dbo.tables.table_id	Foreign table FK_tables_relations_table_fk
dbo.tables_relations.fk_table_id = dbo.tables.table_id Title: Foreign table Name: FK_tables_relations_table_fk
dbo.tables_relations (Table relationships)	dbo.tables (Tables and Views)	dbo.tables_relations.pk_table_id = dbo.tables.table_id	Primary table FK_tables_relations_table_pk
dbo.tables_relations.pk_table_id = dbo.tables.table_id Title: Primary table Name: FK_tables_relations_table_pk
dbo.erd_links (ERD Links)	dbo.tables_relations (Table relationships)	dbo.erd_links.relation_id = dbo.tables_relations.table_relation_id	FK_erd_links_tables_relations
dbo.erd_links.relation_id = dbo.tables_relations.table_relation_id Name: FK_erd_links_tables_relations
dbo.tables_relations_changes	dbo.tables_relations (Table relationships)	dbo.tables_relations_changes.table_relation_id = dbo.tables_relations.table_relation_id	fk_tables_relations_changes_tables_relations
dbo.tables_relations_changes.table_relation_id = dbo.tables_relations.table_relation_id Name: fk_tables_relations_changes_tables_relations
dbo.tables_relations_columns (Tables relations' columns)	dbo.tables_relations (Table relationships)	dbo.tables_relations_columns.table_relation_id = dbo.tables_relations.table_relation_id	FK_tables_relations_columns_relation
dbo.tables_relations_columns.table_relation_id = dbo.tables_relations.table_relation_id Name: FK_tables_relations_columns_relation
dbo.tables_relations_columns_changes	dbo.tables_relations (Table relationships)	dbo.tables_relations_columns_changes.table_relation_id = dbo.tables_relations.table_relation_id	fk_tables_relations_columns_changes_tables_relations
dbo.tables_relations_columns_changes.table_relation_id = dbo.tables_relations.table_relation_id Name: fk_tables_relations_columns_changes_tables_relations

Unique keys

	Key name	Columns	Description
	PK_tables_relations	table_relation_id	Table primary key
Table primary key

Triggers

	Key name	When	Description
	trg_tables_relations_change_track_insert	After Insert	Schema change tracking trigger
-- ============================================= -- Description: Insert relation's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_relations_change_track_insert] ON [tables_relations] 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] 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_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id];
Schema change tracking trigger
-- ============================================= -- Description: Insert relation's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_relations_change_track_insert] ON [tables_relations] 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] 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_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id];
	trg_tables_relations_change_track_update	After Update	Schema change tracking trigger
-- ============================================= -- Description: Insert relation's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_relations_change_track_update] ON [tables_relations] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] WHERE ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[pk_table_id],'') <> [i].[pk_table_id] OR ISNULL([d].[fk_table_id],'') <> [i].[fk_table_id] OR ISNULL([d].[update_rule],'') <> [i].[update_rule] OR ISNULL([d].[delete_rule],'') <> [i].[delete_rule] OR ISNULL([d].[disabled],'') <> [i].[disabled] -- or isnull(d.fk_type,'') <> i.fk_type -- or isnull(d.pk_type,'') <> i.pk_type ) BEGIN UPDATE [tables_relations_changes] SET [valid_to] = GETDATE() FROM [tables_relations_changes] [c] INNER JOIN [inserted] [u] ON [c].[table_relation_id] = [u].[table_relation_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for relation - when relation was updated INSERT INTO [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 [tb].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [d].[name], [d].[update_rule], [d].[delete_rule], [d].[disabled], [d].[fk_type], [d].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], [tb_pk].[schema], [tb_pk].[name], [tb].[schema], [tb].[name], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [tb] ON [tb].[table_id] = [d].[fk_table_id] LEFT JOIN [tables] [tb_pk] ON [tb_pk].[table_id] = [d].[pk_table_id] LEFT JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [tables_relations_changes] [c] WHERE [c].[table_relation_id] = [d].[table_relation_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[pk_table_id],'') <> [i].[pk_table_id] OR ISNULL([d].[fk_table_id],'') <> [i].[fk_table_id] OR ISNULL([d].[update_rule],'') <> [i].[update_rule] OR ISNULL([d].[delete_rule],'') <> [i].[delete_rule] OR ISNULL([d].[disabled],'') <> [i].[disabled] -- or isnull(d.fk_type,'') <> i.fk_type -- or isnull(d.pk_type,'') <> i.pk_type ); END; BEGIN -- insert changes (deleted values) for relation - when relation was deleted INSERT INTO [tables_relations_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_to], [update_id] ) SELECT [t].[database_id], [d].[table_relation_id], [d].[pk_table_id], [d].[fk_table_id], [d].[name], [d].[update_rule], [d].[delete_rule], [d].[disabled], [d].[fk_type], [d].[pk_type], [tb_pk].[schema], [tb_pk].[name], [t].[schema], [t].[name], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [t] ON [t].[table_id] = [d].[fk_table_id] LEFT JOIN [tables] [tb_pk] ON [tb_pk].[table_id] = [d].[pk_table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for relation - when relation was restored INSERT INTO [tables_relations_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
Schema change tracking trigger
-- ============================================= -- Description: Insert relation's changes to schema change tracking tables -- ============================================= CREATE TRIGGER [trg_tables_relations_change_track_update] ON [tables_relations] FOR UPDATE AS -- skip manual objects created through Dataedo application IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] WHERE [source] = 'USER' ) BEGIN RETURN; END; -- check if object property change IF EXISTS ( SELECT TOP (1) 1 FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] WHERE ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[pk_table_id],'') <> [i].[pk_table_id] OR ISNULL([d].[fk_table_id],'') <> [i].[fk_table_id] OR ISNULL([d].[update_rule],'') <> [i].[update_rule] OR ISNULL([d].[delete_rule],'') <> [i].[delete_rule] OR ISNULL([d].[disabled],'') <> [i].[disabled] -- or isnull(d.fk_type,'') <> i.fk_type -- or isnull(d.pk_type,'') <> i.pk_type ) BEGIN UPDATE [tables_relations_changes] SET [valid_to] = GETDATE() FROM [tables_relations_changes] [c] INNER JOIN [inserted] [u] ON [c].[table_relation_id] = [u].[table_relation_id] WHERE [valid_to] IS NULL; -- insert changes (before and after values) for relation - when relation was updated INSERT INTO [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 [tb].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [d].[name], [d].[update_rule], [d].[delete_rule], [d].[disabled], [d].[fk_type], [d].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], [tb_pk].[schema], [tb_pk].[name], [tb].[schema], [tb].[name], 'UPDATED', [cc].[valid_to], [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [tb] ON [tb].[table_id] = [d].[fk_table_id] LEFT JOIN [tables] [tb_pk] ON [tb_pk].[table_id] = [d].[pk_table_id] LEFT JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id] OUTER APPLY ( SELECT MAX([valid_to]) AS [valid_to] FROM [tables_relations_changes] [c] WHERE [c].[table_relation_id] = [d].[table_relation_id] AND [c].[valid_to] IS NOT NULL ) [cc] WHERE [i].[status] = 'A' AND [d].[status] = 'A' AND (ISNULL([d].[name],'') <> [i].[name] OR ISNULL([d].[pk_table_id],'') <> [i].[pk_table_id] OR ISNULL([d].[fk_table_id],'') <> [i].[fk_table_id] OR ISNULL([d].[update_rule],'') <> [i].[update_rule] OR ISNULL([d].[delete_rule],'') <> [i].[delete_rule] OR ISNULL([d].[disabled],'') <> [i].[disabled] -- or isnull(d.fk_type,'') <> i.fk_type -- or isnull(d.pk_type,'') <> i.pk_type ); END; BEGIN -- insert changes (deleted values) for relation - when relation was deleted INSERT INTO [tables_relations_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_to], [update_id] ) SELECT [t].[database_id], [d].[table_relation_id], [d].[pk_table_id], [d].[fk_table_id], [d].[name], [d].[update_rule], [d].[delete_rule], [d].[disabled], [d].[fk_type], [d].[pk_type], [tb_pk].[schema], [tb_pk].[name], [t].[schema], [t].[name], 'DELETED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [t] ON [t].[table_id] = [d].[fk_table_id] LEFT JOIN [tables] [tb_pk] ON [tb_pk].[table_id] = [d].[pk_table_id] WHERE [i].[status] = 'D' AND [d].[status] = 'A'; -- insert changes (updated values) for relation - when relation was restored INSERT INTO [tables_relations_changes] ([database_id], [table_relation_id], [pk_table_id], [fk_table_id], [name], [update_rule], [delete_rule], [disabled], [fk_type], [pk_type], [pk_table_schema], [pk_table_name], [fk_table_schema], [fk_table_name], [operation], [valid_from], [update_id] ) SELECT [t].[database_id], [i].[table_relation_id], [i].[pk_table_id], [i].[fk_table_id], [i].[name], [i].[update_rule], [i].[delete_rule], [i].[disabled], [i].[fk_type], [i].[pk_type], [t_pk].[schema], [t_pk].[name], [t].[schema], [t].[name], 'ADDED', GETDATE(), [i].[update_id] FROM [inserted] [i] INNER JOIN [deleted] [d] ON [i].[table_relation_id] = [d].[table_relation_id] JOIN [tables] [t] ON [t].[table_id] = [i].[fk_table_id] LEFT JOIN [tables] [t_pk] ON [t_pk].[table_id] = [i].[pk_table_id] WHERE [i].[status] = 'A' AND [d].[status] = 'D'; END;
	trg_tables_relations_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_Modify] ON [tables_relations] AFTER INSERT, UPDATE AS BEGIN UPDATE [tables_relations] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE table_relation_id IN (SELECT DISTINCT table_relation_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_Modify] ON [tables_relations] AFTER INSERT, UPDATE AS BEGIN UPDATE [tables_relations] SET last_modification_date = GETDATE(), modified_by = suser_sname() WHERE table_relation_id IN (SELECT DISTINCT table_relation_id FROM Inserted) END

Uses

Name
dbo.tables_relations (Table relationships)
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views) (Foreign table)
dbo.tables (Tables and Views) (Primary table)
dbo.trg_tables_relations_change_track_insert
dbo.schema_updates (Imports/Updates)
dbo.tables (Tables and Views)
dbo.tables_relations_changes
dbo.trg_tables_relations_change_track_update
dbo.tables (Tables and Views)
dbo.tables_relations_changes
dbo.trg_tables_relations_Modify
dbo.tables_relations (Table relationships)

Used by

Name
dbo.tables_relations (Table relationships)
dbo.trg_tables_relations_cols_change_track_insert
dbo.trg_tables_relations_cols_change_track_update
dbo.trg_tables_relations_Modify
dbo.erd_links (ERD Links)
dbo.tables_relations_changes
dbo.tables_relations_columns (Tables relations' columns)
dbo.tables_relations_columns_changes

Exported: 2018-12-06 15:15, Last imported: 2018-11-21 15:11