Oracle trigger 1-Introduction

Oracle Trigger 1-Introduction to Reasons to Use Trigger: ■ Automatically generate calculated column values ■ Log events ■ Gather statistics on table access ■ Modify table data when DML statements are issued against views ■ Enforce referential integrity when child and parent tables are on different nodes a distributed database ■ Publish information about database events, user events, and SQL statements to subscribing ap Plications ■ Prevent DML operations on a table after regular business hours ■ Prevent invalid transactions ■ Enforce complex business or referential integrity rules that you cannot define with constraints I E is poor to Prevent misleading others, I will not explain them one by one. A trigger is stored in a database as an independent object. Unlike stored procedures and functions, a stored procedure and function must be displayed and called before execution, A trigger is started by an event. That is, the trigger runs automatically and implicitly when an event occurs. In addition, triggers cannot receive parameters like stored procedures. ORACLE events refer to the INSERT, UPDATE, and DELETE operations on database tables or similar operations on views. ORACLE extends the trigger function to ORACLE, such as database startup and shutdown. Therefore, triggers are often used to complete the constraints of complex business rules that are difficult to complete due to the integrity constraints of the database, or to monitor various operations on the database to implement the audit function. DML triggers ORACLE can be triggered by DML statements, which can be triggered before or after DML operations, and can be triggered by each row or statement operation. DDL triggers are similar to DML triggers. Because the alternative trigger is in ORACLE, you cannot directly operate on the views created by more than two tables. Therefore, an alternative trigger is provided. Database event triggers ORACLE provides the third type of triggers. Database event triggers are also called system triggers. It can be triggered in ORACLE Database System Events, such as ORACLE system startup and shutdown. Trigger components 1, Trigger name 2, Trigger Statement 3, Trigger limit 4, Trigger operation Design Guidelines: ■ Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement ). for example, use a trigger to ensure that whenever anyone updates a table, its log file is updated. ■ Do not create triggers that duplicate database features. for example, do not create a trigger to reject invalid data if you can do the same with constraints. ■ Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary ). for example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. if a trigger updates global package variables, initialize those variables in a BEFORE statement trigger. ■ Use BEFORE row triggers to modify the row before writing the row data to disk. ■ Use AFTER row triggers to obtain the row ID and use it in operations. an AFTER row trigger fires when the triggering statement results in ORA-2292. ■ If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETEstatement that conflicts with an UPDATE statement that is running, then the database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. the database can do this operation times before the triggering statement completes successfully. each time the database restarts the triggering statement, the trigger fires. the ROLLBACK to SAVEPOINT does not undo changes to package variables that the trigger references. to detect this situation, include a counter variable in the package. ■ Do not create recursive triggers. for example, do not create an after update trigger that issues an UPDATE statement on the table on which the trigger is defined. the trigger fires recursively until it runs out of memory ■ Use DATABASE triggers judiciously. they fire every time any database user initiates a triggering event. ■ If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table: SELECT Username FROM USER_USERS; ■ Only committed triggers fire. A trigger is committed, implicitly, after the create trigger statement that creates it succeeds. therefore, the following statement cannot fire the trigger that it creates: create or replace trigger my_triggerAFTER create on databasebeginnull; END; /■ To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that runs a sequence of operations. each trigger sees the changes made by the previusly fired triggers. each trigger can see OLD and NEW values.