Disabling an application feature with database trigger

Interesting things have been happening around but I did not write down here for quite sometime.

I remember the incident - I got call around 3 am from the ops team when they noticed very high jdbc connection usage. I found that, one of our delete feature taking too much time in db and also causing others to be blocked.

This 'delete' is not such an important feature and we had been thinking to get rid of this permanent delete from database. We had been thinking not to delete any user permanently from database because, we are no longer running a small system. We have FK reference over thousand places in database. A permanent delete checks all the FK references in child tables, anyways. This takes a lot of time even though FK references were indexed. Till then, unfortunately we could never find a suitable window to do the release with the fix. The alternatives was - changing the user status to "deleted" by a flag.

Anyways, the flag change approach requires some programming time and a release schedule, which was not going to happen in a day or two even if we had been locking by this delete that night. User complaining slowness...

I thought, as a quick fix, I can put a trigger and prevent user from using that feature - I mean it's possible to stop user from deleting the records from that table.


CREATE OR REPLACE TRIGGER PREVENT_USER_DELETE
BEFORE DELETE
ON app_user
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20005,'User delete is temporarily unavailable');
END;
/

This simple trigger was a life saver that night. Few users who tried to delete application user permanently, they received application error page though, but the db never got locked in this cascading effect. This bought us some time to improve the feature of permanent delete - changing status flag.

***

Stop user from accidental database damage!

The guys who play with database can not help themselves doing crazy stuffs always. Sometimes we logged in multiple production servers and also test servers simultaneously. These sessions are opened in multiple console tabs. It might be happened, we want to run some operations on test servers but accidentally run those on production boxes. Just imagine, I want to run TRUNCATE TABLE operation on test box but I did it on prod boxes actually - opss! what a disaster!

To avoid this types of blundering actions, I have introduces the following trigger to prevent any kind of unconscious DDLs in production boxes. The trigger will be stored on proper schema always and if any DDL issueed in the presence of the trigger, it will simply slap us :P

CREATE OR REPLACE TRIGGER ddl_restrict_trigger
BEFORE ALTER OR CREATE OR DROP OR TRUNCATE
ON SCHEMA
BEGIN
RAISE_APPLICATION_ERROR(-20001,' You are not authorized to perform DDL. Please contact DBA team.');
END;
/