How to update the current table in the Oralce trigger

The method for updating the current table in the Oralce trigger. This article describes two problems: 1. Differences between the update trigger before and the after. 2. After A field in Table A is updated, the trigger triggers

The method for updating the current table in the Oralce trigger. This article describes two problems: 1. Differences between the update trigger before and the after. 2. After A field in Table A is updated, the trigger triggers

This article talks about two problems: 1. The difference between the update trigger before and the after. 2. After A field in Table A is updated, the trigger is triggered and executed.

-- Create table
Create table TEST_UP_TRI
(
Id number,
NAME VARCHAR2 (20 ),
Age number (2)
)

To update other fields in table.

Example:

Create a trigger for the table. The function is: when the name field changes, set the age field to 20. The trigger is as follows:

Create or replace trigger testtab_up_tri before update of name on test_up_tri
For each row
Declare
Begin
: New. age: = 20;
End;

Shows the test:

The following two questions are explained at the beginning of the article:

1. Differences between after and before.
One is triggered before the record operation, and the other is triggered after the record operation.

For example, a foreign key is defined between tables. When deleting a primary key, you must first Delete the foreign key table, after, after each row affected by the trigger statement is modified and corresponding integrity constraints may be applied, if the trigger restriction is not violated, the trigger action is executed for the current row. Unlike BEFORE row triggers, AFTER row triggers lock rows. Therefore, after statements such as new. age: = 20 cannot be used.

2. Use update... Set is not allowed to update the table. You can only use the new: = ** method. If you use this method, you can compile the table. However, the following error occurs during execution:

ORA-04091: The table ZXL. TEST_UP_TRI has changed and trigger/function cannot read it
ORA-06512: In "ZXL. TESTTAB_UP_TRI", line 5
ORA-04088: An error occurred while executing the trigger 'zxl. testtab_up_tri'

3,: new: = ** can also be replaced by select 20 into: new. age, so that when we find a field, we can directly use select ...... Into: new. age directly updates the specified field.