ArtenSUITE Part 7: Triggers on Delete

ArtenSUITE
A Development Journal
Part 7: Triggers on Delete

For ArtenSUITE I intend to make far greater use of the capabilities of the database than I ever have previously. In the past I have often ignored Triggers and Database Constraints in favour of keeping the rules within my applications. This has been relatively successful, especially when developing software that is going to be used within a controlled environment such as a single corporation.

Software that intends to be unleashed to the masses however needs more control putting into the database where it can stop the Smart Alec with the password and a SQL tool from deleting or modifying data directly, completely bypassing the controls within the application.

So this evening I have been putting some Triggers into my database.

Against each Database Table is a Column called LOCKED. So for example the Main User Table, BAS_USR_MAIN would have a column called USR_LOCKED. If the contents of the LOCKED column equal 1, then the record cannot be deleted. Ever. Under any circumstances. Mostly. This is useful for stopping the Master User ( the Main Admin User that is setup automatically when the system is first used) from deleting his own entry and therefore locking himself out of the system. There are lots of circumstances, in all kinds of tables, where having the LOCKED column be beneficial.

I therefore needed a Trigger to execute BEFORE the row was to be deleted and stop the delete happening if the LOCKED flag was set. This is very easy to accomplish in Oracle. The SQL I used is shown below:



What is more useful about this Trigger is that I have used the RAISE_APPLICATION_ERROR process to pass a user defined error number and message back to the application that attempted to perform the delete. The user defined error numbers can be between -20000 and -20999, the other numbers are reserved by Oracle for their own purposes. My application can check for this error code when a delete is attempted and then display a nicely formatted error message that explains precisely why the record could not be deleted. The message description I have specified in the script is the one that will be seen when an attempt to delete is made from outside the control of my application.

An example of this can be seen in the screenshot below. I logged onto SQLPlus from my Mac and attempted to delete the contents of a table (one record of which) is protected by the Trigger shown earlier. The screenshot shows the results of the attempted delete.



I only have one record in this table at the moment but if there was more than one record the SQL command I issued would have deleted all of the other rows successfully except for the one marked as LOCKED. This is down to my use of the ‘FOR EACH ROW’ statement in the Trigger.

"Self-development is a higher duty than self-sacrifice." - Elizabeth Cady Stanton

www.artenscience.co.uk
Honest Expert Independent Technology Advice for Business