Today more and more SAP on oracle systems are integrated with SAP SLT. SLT creates lots of oracle triggers to do the data replication, but previously database trigger was not often used in SAP oracle environment, so things changed.
Common errors caused by primary sql (sql not in trigger) can still be caused by trigger sql. Some kinds of “constraint/unique violated” (ORA-00001) or performance problems may be caused by trigger, but we totally go to wrong way when doing the troubleshooting. We only focus on the primary segment/table, not consider if there is trigger in the background.
Whenever you cannot find any cause in normal way or weird case, please think about if there is trigger on the object, especially for such warning “constraint/unique violated”, “object does not exist”. If we go to wrong way or take any wrong action, then things will be really bad. If we search SAP note for these kind of error, then we can find one possibility is corruption. I have ever seen that a basis restored their system for such case, but the problem is never solved, because the root cause is trigger. Regarding the performance, a sql command will finally return if its trigger sql finishes. That is to say a primary sql may not be slow, but it is trigger sql is slow.
We can use oracle dictionary dba_triggers and dba_source to get trigger details.
This blog is only heads up to remind us not to forget trigger which is like a black box, which is often forgot.