We help IT Professionals succeed at work.
Get Started

Writing Triggers to maintain data integrity

213 Views
Last Modified: 2013-05-13
I have an Access 2003 application with SQL Server 2012 linked tables.

The "Locations" table has four fields of concern, namely ParentId, WIP, Reserved, OwnedByParent

WIP, Reserved, and OwnedByParent are smallint datatype, can only be either -1 or 0 per a check constraint, and can not be Null.  ParentId can be Null and is an int datatype.

The Requirement: Whenever there is a record with a value for ParentId (i.e. ParentId is not Null) and WIP=0, there needs to also be one and only one record with WIP = -1 and the same values for ParentId, Reserved, and OwnedByParent as that record.

I think this can be accomplished by:
1.) Creating a trigger such that whenever a record with a value for ParentId is inserted, a record is also inserted with WIP = -1 and the same ParentId, Reserved, and OwnedByParent values as the record being inserted if a record meeting that criteria doesn't already exist, and
2.) Create another trigger that rolls back a transaction if a record being inserted has WIP = -1 and there already exists a record with WIP = -1 and with the same ParentId, Reserved, and OwnedByParent values as the record being inserted.

Is there an easier way to accomplish the requirement?  If not how can I write the triggers to do this?  I prefer not to put this code in the front end application because I'd have to include code in any part of the application that is used to maintain data in the locations table, however that is an option if it is too complicated to do with triggers in SQL Server.
Comment
Watch Question
CERTIFIED EXPERT
Commented:
This problem has been solved!
Unlock 1 Answer and 2 Comments.
See Answer
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE