Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 Using triggers for referential integrity

Author  Topic 

notmyrealname

98 Posts

Posted - 2013-02-22 : 13:21:40
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

15732 Posts

Posted - 2013-02-22 : 15:12:00
quote:
I am considering abandoning relationships and using triggers to handle this.
Why?
Go to Top of Page

notmyrealname

98 Posts

Posted - 2013-02-22 : 15:45:24
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

15732 Posts

Posted - 2013-02-22 : 16:35:40
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

98 Posts

Posted - 2013-02-25 : 08:38:09
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

15732 Posts

Posted - 2013-02-25 : 17:29:08
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

98 Posts

Posted - 2013-02-27 : 08:24:02
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

15732 Posts

Posted - 2013-02-27 : 09:58:30
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
   

- Advertisement -