Keep Business Rules out of Triggers

My old team needed some assistance with a problem that was happening in production. Rows from a database table were mysteriously disappearing when the users ran a certain application. The developers swore that their code did not delete these records.

Since I trust nobody, I scanned the source code of all the database triggers in the system. Sure enough, there was a trigger on another table that deleted the records when a certain field got set to a value. Oh snap. This is just poor design. That's not what triggers are for.

There are two lessons to learn from this. One is to practice solid design and keep business rules out of the database triggers. The second is that the client server developers need to keep an eye on the code in the database triggers.

Promotion Woes

I had to fix a problem they were having using a back end process in production. Seems a processing command was resulting in an Oracle error. I investigated and found that the key stored procedure was commented out of the package. So I reenabled the procedure and thought we were good to go.

I got a call from our DBA. He said my code changes would not work in some of the other database schemas. When I got in to work, I found that the other schemas were missing a key column in the staging database table. That should never happen. We got a database configuration management issue.

So I thought we could just add the column everywhere. Some more analysis showed me this would not correct the whole problem. In the end I decided to recode the stored procedure to use another technique to derive the necessary data using some assumptions. This whole episode made me a bit leery about the state of our databases.