"I work for a nationwide retailer," Jody writes, "In one of the databases I work with frequently is a table called PRODUCTS which contains information about every item we sell, and on this table there's a trigger called PRODUCTS_TRIGGER which fires on every insert, update, or delete. One day while working on some unit tests I needed to insert a row into PRODUCTS, work with it, and then delete it when the test was complete, but the DELETE failed because of a problem in PRODUCTS_TRIGGER. When I looked at the code for this trigger I found the following sequence of comments:

    -- <name redacted>  mm/01/yyyy  This trigger was preventing deletes because
    --                              it's trying to insert NULL into the primary
    --                              key of the PRODUCTS_AUDIT table on DELETES.
    --                              Corrected problem by using :old.PRODUCT_ID
    --                              (which has a value) instead of :new.PRODUCT_ID
    --                              which is NULL when doing a DELETE.
    -- <name redacted>  mm/10/yyyy  <Name of VP in charge of security redacted>
    --                              insists this trigger be changed back to how it
    --                              was to "prevent a rogue process from deleting
    --                              products".

Jody continues, "Translation - rather than put something in place that would correctly keep unauthorized users and applications from deleting PRODUCTS data while allowing authorized activity, we're counting on a bug in a trigger to keep us safe from the hordes of evil mutant zombie plasmoids who are diligently searching for nefarious ways to delete our product data. Brilliant!"

"As an interesting side note, there are about 1.1 million rows in the PRODUCTS table. 700,000 of these products have not been sold in over a year. In other words, more than 50% of the data in the PRODUCTS table is obsolete, but can't be purged! Sheer genius!"

"To get my unit tests working I just added an ALTER TRIGGER PRODUCTS_TRIGGER DISABLE to the test script, with a corresponding ALTER TRIGGER PRODUCTS_TRIGGER ENABLE after the delete was successful. I sure hope the hordes of evil mutant zombie plasmoids don't read this!"

[Advertisement] BuildMaster allows you to create a self-service release management platform that allows different teams to manage their applications. Explore how!