Writing Triggers or implementing actions by programming?

Question

I am a newbie. I am writing an application in Java which connects to Oracle 9i database. I need to enforce some rules in my application like:

Chair of Committee A must be added/deleted as a Member in Committee B.

There are around 10 such rules that need to be enforced. I am in a dilemma whether to enforce these rules by writing triggers or by programming within my application.

I appreciate any suggestions.

Thanks,

Answer 1

I may be mistaken, but what you are talking about sounds like an actual business rule (Chairman of Comittee A must be a member of B, or cannot be a member of B). IMO, that should be handled by the application and not the database.

Answer 2

Rules that specifically address data integrity get enforced in the database. Rules that are more logical/business rules I don't think belong in the db, because

• they're invisible to the application code
• they're more likely to be mutable, and this can be inconvenient if they're baked into the db.

The type of thing I'd more likely expect to see enforced by the DB would be things like "Committee record name can't be null", i.e. things that are necessary for data integrity.

Answer 3

Assuming these are rules that relate to a policy of the organization in question - i.e. "all committee chairs must be a member of at least one other committee", or "no committee must have less than two members", then you should probably put them in Java. Java is a more flexible programming environment than SQL and an RDBMS.

Say your rule is "no committee chairman can be a member of any other committee". You might implement it like this:

public class Committee {

    private Person chairman;
    private Set<Person> members;

    public Person getChairman() {
        return chairman;
    }

    public void setChairman(Person person) {
        for (Committee other : person.getMemberships() {
            other.removeMember(person);
        }
        chairman = person;
        addMember(person);
    }

    public void addMember(Person person) {
        members.add(person);
        person.addMembership(this);
    }

    public void removeMember(Person person) {
        members.remove(person);
        person.removeMembership(this);
    }

    public Set<Person> getMembers() {
        return new HashSet<Person>(members);
    }
}

public class Person {

    private Set<Committee> memberships = new HashSet<Committee>();

    public Set<Committee> getMemberships() {
        return new HashSet<Committee>(memberships);  // Return a copy, not the original
    }

    public void addMembership(Committee committee) {
        memberships.add(committee);
    }

    public void removeMembership(Committee committee) {
        memberships.remove(committee);
    }

}

You could then write unit tests for Committee to verify that the rules for chairmanship are working, and they would run very fast, because they wouldn't need the database.

public class CommitteeTest {

    @Test
    public void testChairmanship() {

        // Set up the initial conditions
        Person person = new Person();
        Committee other = new Committee();
        Commiteee commitee = new Committee();
        other.addMember(person);

        committee.setChairman(person);
        assertTrue(committee.getChairman().equals(person));
        assertFalse(other.getMembers().contains(person));
    }
}

(You might want to use a mock object for the Person, since this unit test is supposed to test only Committee.)

As you add more rules, you would add more unit tests, and then would continue to run fast.

Answer 4

I think that if a constraint can be enforced in the database then it should be, however the application should try to enforce it first and the database should be there only as a backup to that -- a fail-safe, if you like.

However there are some constraints that are difficult to enforce in an RDBMS and this example is one of them. It's difficult because the database's native constraint types (unique, check, references etc) do not allow you to enforce it and you would have to revert to a trigger or materialized view or somesuch. Triggers are terrible at enforcing constraints because query isolation levels can cause some violations to slip by, and they also cannot read or modify the affected table (the mutating table problem).

to be fair though application-level constraint checking suffers from the same problem with query isolation levels, hence the use of database constraints as a backup.