Use Oracle triggers

Table of Contents 1 is an ORACLE trigger used in a scenario. It is a series of DB operations that are passively triggered when the conditions are consistent. A trigger is a passive action triggered by the Oracle database. Unlike a stored procedure, a stored procedure can be called by external code or trigger, while a trigger is a passive action of the database. For example, when you need to save a table record and record the log information of the Save a table to table B, the trigger will meet your requirements. Two environments and Backup command environment: This article is based on centos and Oracle 10 Gb debugging. Backup command: Stop Oracle service other Oracle commands and related settings: Three syntax introduction trigger syntax create [or replace] trigger trigger_name -- create or replace a trigger, trigger_name specify the trigger name {before | after | instead of} -- specify when to execute the trigger {insert | Delete | update [of column [, column…]} -- Specifies the action to trigger the variant table on {[schema.] table_name | [schema.] view_name} -- specify the affected table, schema, or view [Referencing {old [as] Old | new [as] New | parent as parent}] -- the old modifier completes the first step of the access operation value: [for each row] -- specifies the row-Level Trigger or the expression triggers [When condition] -- specifies the trigger condition declare -- declares the variable -- variable; begin -- set the code segment to be executed after the trigger -- trigger_body; end; Purpose:-1, force-Execute business rule. -2. Update data in other tables. -3. Mark the data column for processing. -4. events that have occurred with signals. Note:-1. rollback, commit, savepoints, and transaction control are not supported. For details, refer to-2. Long is not supported, long raw variable-3, after method is more efficient than before-4, row-level triggers cannot perform select, insert, update, delete operations on the current table on the trigger-5,: new,: old, the parent keyword can only be used in a row-Level Trigger [for each row]-6,: new,: old. The colon Before the keyword can only be used in the trigger. that is, the trigger does not require a colon in vitro, for example, in the when clause-7,: new,: Old, pseudo record, but the defined rowtype variable cannot accept: new, or: old, cannot pass: New or: Old to accept-8 of the rowtype, the code size must be smaller than 32kbold and new details: the old modifier specifies the value at the top of an access operation. The new modifier specifies the value in the column after the access operation is completed. The values of New and Old are used to refer to the value of the data field: old: newdelete deleted Value nullinsert null created value update old value new value used in begin, must be added with a colon: When and referencing do not need to add a colon: in a process or function-8, trigger data dictionary: user_triggers-9, mutating table and constrainting table mutating table are the tables currently modified by the DML statement, for triggers, the variant table is defined on the table. because the table that executes Delete cascade to reference integrity constraints update is also a variant table, the constraint table is a table that needs to be read by implementing the reference integrity constraints. -10. The SQL statement in the trigger cannot perform the following operations: read or modify any variant table of the trigger statement, including the primary key and foreign key in the constraint table for reading or modifying the trigger table, unique column-11, because the select, update, delete, and update statements cannot be used in the trigger table in the row-Level Trigger, but statement-Level Trigger can be used, but to use: new ,: the old keyword must use a row-Level Trigger. To use the new, old keywords, select, update, delete, in the update statement, we can define two triggers in a table: one row-Level Trigger and one statement-Level Trigger. In the row-level trigger, the new, old keyword is used, and the select clause is used in the statement-Level Trigger, update, insert, and delete statements. For how to record the value of new, we can use package and
Use the table data type. -12. Sequence of Row-level triggers and statement-level triggers: Before the statement level, before the row level, after the row level, and after the statement level. You do not need to add a colon Before the new clause in the referencing clause. This clause must be placed before the for each row clause. -13. the row-level trigger has the for eache row keyword. Otherwise, the trigger is a statement-level trigger. The trigger is enabled selectively and the keywords deleting, updating, inserting-If trigger is executed for delete, true is returned. If trigger is executed for update, true is returned. If trigger is executed for insert, true is returned. ☆create a trigger trigger: the trigger named demo_trigger above defines to insert a record to the user_info table when the user table record is updated. ☆Pre-trigger execution: Replace the "after" keyword in "triggers" with "before. If it is set to "after", it is completed. See the previous example. ☆Specify the trigger action: After the trigger action is specified to successfully update the user table, you can also specify the trigger to be executed during insertion, update, or deletion. ☆use: New and: create or replace trigger update on "user" referencing old as "user_before_updated" New as "user_after_updated" for each rowdeclare begininsert into user_info values (sysdate, "update a record into user table. ",: user_before_updated.userstatus,: user_after_updated.userstatus); end; referencing old as" user_ B Efore_updated "New as" user_after_updated "is used to record records in the User table before and after the update, and specify aliases. These aliases can be used in the code block to reference record values, such as user_before_updated.userstatus. The referencing clause must be placed before the for each row clause. ☆Specify the trigger condition create or replace trigger demo_triggerafter update on "user" referencing old as "user_before_updated" New as "user_after_updated" for each rowwhen (New. age> = 60 or old. age> = 60) Declare begininsert into user_info values (sysdate, "update a record into user table. ",: user_before_updated.userstatus,: user_after_updated.userstatus); end; the preceding example uses when (New. age> = 60 or old. age> = 60) only age with a new record or old record> = 60, the code segment specified by the trigger is executed. ☆Declare the variable create or replace trigger demo_triggerafter update on "user" referencing old as "user_before_updated" New as "user_after_updated" for each rowwhen (New. age> = 60 or old. age> = 60) Declare olduserstatus varchar2 (20); newuserstatus varchar2 (20); dtime number; begininsert into user_info values (sysdate, "update a record into user table. ",: user_before_updated.userstatus,: user_after_updated.userstatus); end; in the preceding example, the olduserstatus, newuserstatus, and dtime variables are declared through declare. You can also use them in the code segment, as follows: create or replace trigger demo_triggerafter update on "user" referencing old as "user_before_updated" New as "user_after_updated" for each rowwhen (New. age> = 60 or old. age> = 60) Declare olduserstatus varchar2 (20); newuserstatus varchar2 (20); dtime number; begin olduserstatus: = 'olduserstatus '; -- calculate the current system time as a numeric value and assign it to dtime select (sysdate-8/24-to_date ('2017-01-01 ', 'yyyy-mm-dd ')) * 86400000 into dtime from dual; insert into user_info values (dtime, "update a record into user table. ", olduserstatus,: user_after_updated.userstatus); end; ☆create or replace trigger demo_triggerafter insert or update on "user" referencing old as "user_before_updated" New as "user_after_updated" for each rowwhen (New. age> = 60 or old. age> = 60) Declare olduserstatus varchar2 (20); newuserstatus varchar2 (20); dtime number; begin olduserstatus: = 'olduserstatus '; -- calculate the current system time as a numeric value and assign it to dtime select (sysdate-8/24-to_date ('2017-01-01 ', 'yyyy-mm-dd ')) * 86400000 into dtime from dual; If updating then
Insert into user_info values (dtime, "update a record into user table. ", olduserstatus,: user_after_updated.userstatus); elsif inserting then insert into user_info values (dtime," insert a record into user table. ", olduserstatus,: user_after_updated.userstatus); end if; end; use if in the code segment" begin end "to determine whether the current action is update or insert and execute different code segments. For details about the returned values of the trigger action, refer to the 13th key points. 6. Special reminder: Calculate the current system time into a numeric value and assign it to dtime -- current_timestamp (3) returns the value of select (optional imestamp-8/24-to_date ('1970-01-01 ', 'yyyy-mm-dd') x 1970) + (to_char (current_timestamp (3), 'ff ') from dual; assign values to variables using SQL: assign values using the select into variable name. When to use a colon to use variables: For aliases defined for old and new after referencing, use Colon: to reference them in the code block; the variables defined in declare do not need to use colons in the code block; the use of new and old in the when clause does not need to use colons. To assign a value to a variable, you must use the colon equal to sign: = to assign a value.