Some Problems of SQL Triggers:
Many people think that PL / SQL triggers are one of the most powerful tools in Oracle databases. In fact, they are, but there are two fundamental reasons why, personally, I try to avoid using triggers when implementing my projects in PL / SQL.
Maintenance issues:
In the long term, the use of triggers often causes great headaches when thinking about maintenance. As they are pieces of code that only occur as a result of another operation being performed, it is very common for people to forget that the triggers are there, and review the code thinking about all the pieces of the database that may affect them, is made little less than impossible.
This problem is very easy to understand. A PL / SQL expert who does not know an application made in PL / SQL and SQL code can easily understand what a stored procedure does, but if our system uses triggers routinely, then that procedure can trigger hundreds of other types of processes and changes in the database that are not detectable with the naked eye.
In conclusion, triggers make understanding a system difficult, make maintaining it more difficult than normal, and create confusion by staying hidden in the database schema. Many people consider triggers to be like DDL statements, and just as there is no need to review a CREATE TABLE statement, there is also no need to review the code for a PL / SQL trigger. Actually, triggers are subroutines that are called over and over again, the code of which must be checked in the same way that packages and stored procedures are checked.
Incorrect use of triggers:
In many cases that I have analyzed, PL / SQL triggers have been implemented incorrectly. Trigger code often includes huge errors that the programmer was not able to foresee or anticipate. Let’s see some examples of this type of incorrect implementation.
On occasion, I have come across triggers that perform operations like this:
: new.full_name: =
: new.name || ' '||: new.a lastname;
The first thing that comes to mind when viewing a line of code like the one above is that the column full_name should be a column of a view (or in Oracle 11g, a virtual column. The column full_name is the result of a trivial function, with almost no processing cost and, if necessary, could even be indexed. There is no reason to store the result of the function in the physical table.
Also, let’s imagine that for some reason we want to change the value of the column full_name for some records, simply the trigger will not let us do it and, if we do not know its existence of it, we will ask ourselves a thousand times why the field does not update when running the UPDATE command. The trigger will create, at least, confusion, and, hopefully, we will realize its existence and that we must disable it in order to carry out the desired field update.
Another of the incorrect uses of triggers is to use them to send emails to users when a record is inserted or updated in a table. Let’s look at the following trigger:
SQL> CREATE TRIGGER send_mail
2 AFTER INSERT ON orders
3 FOR EACH ROW
4 BEGIN
5 UTL_MAIL.send
6 (sender => '[email protected]',
7 recipients => '[email protected]',
8 subject => 'New order' ||: new.num_orido,
9 message => 'New order received');
10 END;
11 /
Trigger created.
The trigger is syntactically correct, it will compile without problems and, if a new order is inserted in the “orders” table, our users will receive an email indicating it. However, the trigger has a serious problem. Someone has wondered what will happen if we insert a hundred records in the mentioned table and, for whatever reason, we decide to undo the inserts by executing the ROLLBACK command. Well, simply what will happen is that we will have sent a hundred emails to the users indicating that there are a hundred new orders that do not really exist.
In this sense, it should be noted that, for this type of situation to occur, it is not necessary to invoke the ROLLBACK yourself, but rather that the Oracle database undoes INSERT, UPDATE, MERGE or DELETE operations, without the need for one to do so. Indicate personally.
We can say that practically any PL / SQL trigger that calls UTL_ functions (like UTL_FILE, UTL_HTTP, UTL_MAIL, UTL_SMTP, UTL_TCP, etc.) will be incorrectly implemented. In conclusion, with triggers you should not perform any operation that cannot be undone later.
Triggers can also cause problems when programmers don’t properly understand one of the most interesting features of Oracle databases, the fact that reads don’t block writes, and that writes don’t block reads. This becomes especially critical when triggers are used to force a certain rule to be followed. All this will be the subject of another later article.
When you want to make a strong Oracle DBA career then you should be aware of database services and other database technology. Without having knowledge of Oracle internals , Oracle performance tuning, and skill of Oracle database troubleshooting you can’t be an Oracle DBA expert. This expert DBA Team club blog always provides you latest technology news and database news to keep yourself up to date. You should need to be aware of Cloud database technology like DBaaS. All Oracle DBA tips are available in a single unique resource at our orageek. Meanwhile, we are also providing some sql tutorials for Oracle DBA. This is the part of Dbametrix Group and you would enjoy more advanced topics from our partner resource.
