quote:

---

I am considering abandoning relationships and using triggers to handle this.

---

ALTER TRIGGER Projects_Update
ON dbo.Projects
FOR UPDATE
AS

	-- Verify Project format.  Must be [##-###]
	IF EXISTS (SELECT Project
				FROM Inserted
				WHERE NOT (Project LIKE '[0-9][0-9][-][0-9][0-9][0-9]'))
		BEGIN
			RAISERROR ('Project is invalid format.  Must be [00-000].', 16, 1);
			ROLLBACK TRANSACTION;
			RETURN 
		END

	-- Declare variables
	DECLARE @Table nvarchar(50)
	DECLARE @SQL nvarchar(max)

	SET @Table = ''

	-- The Inserted trigger table cannot be referenced in dynamic SQL therefore a temporary #Projects table is
	-- created to store the deleted projects
	SELECT  Inserted.Project AS Project_New, Deleted.Project AS Project_Old
	INTO    [#Projects]
	FROM    Inserted INNER JOIN
	        Deleted ON Inserted.Project <> Deleted.Project

	-- Loop through all tables in the database that have a Project column
	WHILE @Table IS NOT NULL
		BEGIN									
			SELECT     @Table = MIN(INFORMATION_SCHEMA.COLUMNS.TABLE_NAME)
			FROM         INFORMATION_SCHEMA.COLUMNS INNER JOIN
									INFORMATION_SCHEMA.TABLES ON INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = INFORMATION_SCHEMA.TABLES.TABLE_NAME
			WHERE     (INFORMATION_SCHEMA.COLUMNS.COLUMN_NAME = 'Project') AND 
						NOT (INFORMATION_SCHEMA.COLUMNS.TABLE_NAME = 'Projects') AND 
						(INFORMATION_SCHEMA.TABLES.TABLE_TYPE = 'BASE TABLE') AND 
						(INFORMATION_SCHEMA.COLUMNS.TABLE_NAME > @Table)
									
			IF	NOT (@Table IS NULL)
				BEGIN	
					-- Update the Project values in the child tables								
					SET @SQL = 'UPDATE  {0}
								SET     {0}.Project = p.Project_New
								FROM	{0} INNER JOIN
										#Projects AS p ON p.Project_Old = {0}.Project'			

					SET @SQL = REPLACE(@SQL, '{0}', @Table)

					EXEC SP_EXECUTESQL @SQL

					-- Update the WorkOrder values in the child tables
					IF EXISTS (SELECT     TABLE_NAME
					          FROM         INFORMATION_SCHEMA.COLUMNS
					          WHERE     (TABLE_NAME = @Table) AND (COLUMN_NAME = N'WorkOrder'))
						BEGIN							
							SET @SQL = 'UPDATE	{0} 
										SET     {0}.WorkOrder = SUBSTRING(p.Project_New, 4, 3) + SUBSTRING({0}.WorkOrder, 4, 3) 
										FROM	{0} INNER JOIN
										#Projects AS p ON p.Project_New = {0}.Project'			

							SET @SQL = REPLACE(@SQL, '{0}', @Table)

							EXEC SP_EXECUTESQL @SQL
						END
				END
		END	

quote:

---

New tables are being add often. At least one a week.

---

quote:

---

Also, i have a unique situation where the project format is 01-234 and the workorder format is 234000 where the first three numbers are the last three numbers of the project.

---

quote:

---

If the Project value is updated then i go through all of the tables in the database that also have a Project column and execute a dynamic update query to update the child table Project values

---