In csdn, it helps others write a simple trigger to update data, which involves several problems.
Create or replace trigger tri_a
After insert
On test11
Declare
Pragma autonomous_transaction; ---- after declare) indicates free transaction processing.
Begin
Update test11 set name = Replace (name ,'','');
Commit;
End;
--------------
First, you use TRIM. You can only filter spaces on both sides, so replace it with replace;
When using the trigger, you can only update the historical data. This operation is invalid;
Create or replace trigger tri_a1
Before insert on test11
For each row
Begin
: New. Name: = trim (: New. Name );
End;
we all know that a trigger does not require a commit and cannot be written. The trigger and the DML that triggers it are the same transaction.
DML is committed, the trigger operation is also submitted, so no commit is required; otherwise, an error message is generated.
of course, if you must write a commit statement in the trigger, you can also use Oracle's autonomous transaction to process it. The autonomous transaction is equivalent to a subtransaction in a transaction.
under normal circumstances, Oracle requires that DDL (Create/alter/drop) Statements and commit/rollback statements cannot be run in triggers, because DDL operations are implicitly submitted, A trigger does not allow commit. For example, if you add a DDL statement to the trigger, this implicit commit will cause an error message. However, in special circumstances, you need to use DDL statements in the trigger.
the following solutions can be taken:
1. You can add Pragma autonomous_transaction to the trigger (after declare), which indicates that a free transaction is processed.
example:
Create or replace trigger t_create before insert on t_tax_ins_bd
for each row
declare
Pragma autonomous_transaction;
nrdsid varchar (500): = '';
begin
you can solve the issue that the trigger cannot contain DDL statements by using the preceding method!