Summary of Oracle triggers

Oracle trigger Summary 1. Trigger definition: A trigger is a special Stored Procedure triggered by a specific time in the database. It is not started by program bars or manually. The execution of triggers can be triggered by insert, delete, update, and other operations on a table. triggers are often used to enhance data integrity constraints and business rules. 2. Trigger: A trigger can query other tables or contain complex SQL statements. It is mainly used to force complex business rules or requirements. For example, you can use a trigger to control whether to allow new orders to be inserted based on the customer's current account status. Triggers can also be used to forcibly reference integrity so that the relationships between these tables are retained when data is added, deleted, or updated in multiple tables. 3. trigger functions: 1) Allow/restrict table modifications 2) automatically generate derived columns, such as auto-increment fields 3) Force Data Consistency 4) provide audit and log records 5) prevent invalid transaction processing 6) enable complex business logic 4. TRIGGER creation Syntax: SQL code DELIMITER | CREATE TRIGGER '<databaseName> '. '<triggerName>' <[BEFORE | AFTER]> <[INSERT | UPDATE | DELETE]> ON <tableName> for each row begin-your SQL END | 5. trigger understanding: Each trigger is a hidden stored procedure. The hidden code is unfriendly to developers. If you are looking at someone else's program, you will always feel that something is missing. Some actions are hidden in the trigger for a long time! I went to the trigger and found DML and other hidden code for other tables. Is it too big? This chain trigger increases complexity and is easy to get out of control. At first, I had done this kind of thing. I felt that the system was full of exquisite organs and had a sense of accomplishment. Later I discovered that this was a nightmare and a trap for maintenance. Because the trigger is hidden, you do not know if it is not executed. If a trigger fails to be compiled, DML still succeeds without sound! The stored procedure is not like this. The trigger cannot be bypassed. If you have a product running, you need to record the historical data of the fault time. The INSERT trigger has some actions you don't want, which is hard to do. The trigger also has the mutating table problem. Many people think about this issue as autonomous transactions, but they do not know this is a bad idea. Self-governing transactions make the data you see old, because the modifications made by the primary firm have not yet been submitted. Multi-row DML is triggered multiple times to generate a large number of small transactions, which can easily lead to deadlocks. When batch operations and multiple triggers are triggered, the trigger is less efficient because it is equivalent to executing a PL/SQL statement each time. it can often be converted into several equivalent SQL statements, which greatly improves the efficiency.