SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Using triggers for referential integrity
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

notmyrealname
Yak Posting Veteran

97 Posts

Posted - 02/22/2013 :  13:21:40  Show Profile  Reply with Quote
Hi,

Has anyone ever user triggers instead of relationships to manage data integrity? I am considering abandoning relationships and using triggers to handle this.

Any advice?

Thanks.

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/22/2013 :  15:12:00  Show Profile  Visit robvolk's Homepage  Reply with Quote
quote:
I am considering abandoning relationships and using triggers to handle this.
Why?
Go to Top of Page

notmyrealname
Yak Posting Veteran

97 Posts

Posted - 02/22/2013 :  15:45:24  Show Profile  Reply with Quote
I knew that would be the first response

Just to set the scene, let's say we have some tables. Projects, WorkOrders, Parts, etc... The data trickles down in this order. These are the key players in my database. Many of my relationships in many different tables depend on this data.

New tables are being add often. At least one a week. One example of a trigger that i have created is on the Projects table FOR DELETE. 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. I could do some of this with cascading relationships but in some cases the different primary keys prevent me from doing this. 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. Using this trigger i am also able to update all workorder values for all tables that have a workorder column appropriately. I could not do this with a relationship.

So i guess my main reasons are:

I like the idea of setting up the trigger to manage all existing table and any new tables in the future.

It is easier to manage. All my referential integrity is handled in one place.

I can use the trigger to validate the data.

It provides additional functionality that i cannot accomplish (as far as i know) with relationships.

The one issue i am having is that in some cases where a project is large, my trigger is timing out. If i were to perform each update individually i could, but the trigger considers all of the updates to be one transaction. I do wonder if there is a workaround to this.

Here is an example trigger:


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	

Thanks for the advice.

Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/22/2013 :  16:35:40  Show Profile  Visit robvolk's Homepage  Reply with Quote
The first question/warning sign:
quote:
New tables are being add often. At least one a week.
Why are you adding new tables?
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.
This sounds more like a presentation issue rather than defining foreign or primary keys. You could probably make your key columns plain integers, and maintain the project & workorder numbers in separate columns, possibly computed columns. I've done this for a similar workorder database a few years back.
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
By "Project value" do you mean the project number? Why would that change? Is the update dynamic SQL only because the "child" tables vary by project? If there's only one master Projects table, and all the child tables key off it, then a foreign key with cascade will be logically easier to program for, and should perform better than triggers assuming proper indexing.
Go to Top of Page

notmyrealname
Yak Posting Veteran

97 Posts

Posted - 02/25/2013 :  08:38:09  Show Profile  Reply with Quote
The app is fairly new and was designed for our production department. Now several other departments want to get involved and they require different data. Most of the new tables will still have some combination of the project, workorder, or part fields as keys. The app was designed to "piggy back" a third party SQL database so we can extend additional functionality. The data tends to repeat itself quite a bit. For instance the WorkOrders table has a primary key of Project, WorkOrder. There is no unique ID to reference in other tables so the Project, WorkOrder values are repeated in many other tables. The data is then joined by this pair. We anticipate breaking away from this third party app in the future at which time i have been given the green light to redesign the database. I think my relationships would work very well with ID based data. In this case if i were to make changes to Project or WorkOrder info no additional cascading updates would be required because everything simply references the ID's. Do you think this would be the nominal way to handle this? As it stands right now, we need many cascading relationships to maintain referential integrity. I am having some difficulty creating some of the relationships because the data already has many conflicts. I can resolve many of them by ensuring that the parent tables have the necessary data. Or, this is where my idea of using a trigger comes in. Do you think i would be better trying to tackle all of the conflicts and adding relationships? Also, without proper ID's to relate will i still run into timeout issues if i try to update large datasets?

Thanks again for your help.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/25/2013 :  17:29:08  Show Profile  Visit robvolk's Homepage  Reply with Quote
For any table that has Project and WorkOrder columns, make those the primary key (primary key doesn't have to be a single column). You can build foreign keys on multiple columns as well, as long as they are unique in the referenced (parent) table. If they need additional columns for uniqueness, make sure those columns are available in referencing (child) tables.

You don't need an FK from Project to every referencing table as long as the hierarchy can be maintained (e.g. Projects->Workorders, Workorders->Parts, Parts->Subassemblies, etc.)

Also, I'd recommend making WorkOrderID the primary key of WorkOrders and make that the only key for any FKs to other tables based on a workorder. This would be an integer/identity and would not have a pattern like you mentioned earlier. You can also calculate a workorder # and store that in Workorders, then join in a query if you need it.

From the state of your current data I don't think triggers will ultimately work to provide referential integrity, certainly not as well as foreign keys. If everything's in one database, and all you need the triggers for is RI, then just do foreign keys instead. If you don't clean it up now and put FKs on it, you'll never have clean data and will always have to review it. Especially if people are adding new tables and new data all the time. An ounce of prevention is worth tons of cure.

Using INSTEAD OF triggers might be useful if you need to do key lookups before inserting data into child tables. That way you can still have real FKs. Typically though it's better to have a stored procedure handle all inserts and do all the necessary lookups.

As far as performance goes, cascading should not typically be necessary. Keys are not meant to be updated, at least not frequently enough to worry about how they'd perform. A column that needs frequent updates should not be a key unless there's no other option.
Go to Top of Page

notmyrealname
Yak Posting Veteran

97 Posts

Posted - 02/27/2013 :  08:24:02  Show Profile  Reply with Quote
Great advice. Thank a ton. I will definitely tackle cleaning things up now before they get out of hand.

The only last question i have is about the cascading relationship again. I understand what you are saying about keys no being updated. But what about deleted? Will deleting a project that causes several hundred thousand other child records to be deleted work ok? I have received timeouts in the past but haven't diagnosed them enough to know that they are related to the cascading delete. Sometime my users try to delete a project and get timeout errors. I will just go into the database and delete the project manually by tackling smaller blocks of data. Any suggestions on this? It doesn't happen too often but if my app allows a user to delete past projects i would like the option to work.

Thanks again.
Go to Top of Page

robvolk
Most Valuable Yak

USA
15655 Posts

Posted - 02/27/2013 :  09:58:30  Show Profile  Visit robvolk's Homepage  Reply with Quote
Instead of deleting, why not have a status column in Projects and update it to "I" or "Inactive"? There's no real reason to cascade deletes all the way down if it's only the Project that's affected. If you have to clean out old data, do the delete during a maintenance window where it won't impact users.

Frankly I've never used cascading deletes in SQL Server, and only rarely in MS Access, and had enough scares then to swear them off. Having a stored procedure manage that kind of thing is much better IMHO, there's a lot more control than just a blind cascade.
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000