Oracle: Triggers, autonomous transaction trigger

Create or replace trigger Tri_fc83_insert  before INSERT on FC83 for each  ROWdeclare  PRAGMA autonomous_ TRANSACTION; BEGIN  :new'1';    from fc83    where afc001 =:new. afc001     =:newafa031     =:  Newafc210;  commit; END Tri_fc83_insert;

Oracle trigger self-made transactions, the purpose is simple, when inserting data, first insert a afc212 (original value of 0) of the value of 1 of data, and then the afc212=0 data deleted.

As follows: The red is added to solve the problem (that is, the resolved transaction cannot be submitted)

The SQL triggering tri_fc83_insert is as follows

 insert into fc83 (afc001, afa031, afc163, afc187, afc183, afc157, afc181, afa040, AFC1 afa192, afa051, afa053, afc166, afc155, afc153, afc154, afa183, afa184, afa185, afa032, afa052, afa091, afc015, aaa010 , afc210, afc211, afa501, afa503, aaa102, afc212, afc501)  select   afc001, afa031, afc163, afc187, afc183, afc157, afc181, afa040, afc180, afa192, afa051, afa053, afc166, afc155, afc153, afc154, afa183, afa184, afa185, afa032, afa052, afa091, afc015, aaa010, afc210, afc211, afa501, afa503, aaa102,  
   
     '  
    1  
     ' , afc501 
   

*-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-**-*-*-*-*

Format of the trigger:

CREATE[OR REPLACE]TRIGGERTrigger_name{before| After} {UPDATE|insert| delete| Select} on Table_name[ " [follows Another_trigger][enable| Disable][when Condition]declare xxxx begin null;[exception]end;    

This is the general trigger format.

before| After: Select whether the trigger occurs before or after a DML operation.

It should be possible not to know that triggers can not work on a SELECT.

For each row: Specifies the row trigger, which applies only to Update,insert,delete.

Follows, specifying the order of the triggers, used after 11g.

enable| DISABLE: Specifies whether the trigger is enabled or disabled. (Default boot)

or by using the following statement

ALTER TRIGGER trigger_name {ENABLE| DISABLE};

When: Specifies the conditions that must be met when the trigger is executed.

When a table is deleted, the triggers defined on this table are also deleted.

Attention:

1. Triggers may not execute transaction control statements, E.g.commit, SavePoint, ROLLBACK. When the trigger executes yes, all actions performed are referred to as part of the transaction. When a transaction is committed or rolled back, the trigger is committed or rolled back. If you want to jump out of this rule you can pass an autonomous transaction .

2. A trigger invokes any function or procedure that might not execute a transaction control statement. Unless an autonomous transaction is included.

3. Declaring a long or long RAW variable in a trigger is not allowed.

The trigger contains two pseudo-records : New,:old, including colons when used to identify new data (when insert), older data (when delete), or both in update. is set to NULL if it is not logically present

The two can be seen as record use.

The boolean function check that is provided in the trigger is the state in DML that is triggered , as follows:

CREATETRIGGERTrigger_name afterUPDATEORDELETEOntable_nameDECLAREV_typevarchar (20 ); begin if UPDATING then< Span style= "color: #000000;" > V_type: =  updating< Span style= "color: #ff0000;" > ' 

Autonomous transactions:

An independent transaction initiated by another transaction, usually referred to as the primary transaction. That is, an autonomous transaction might execute multiple DML statements and commit or roll back operations without committing or rolling back the DML statements executed by the primary transaction.

Example:

There is a trigger that is triggered after the table performs an update to record audit data. Suppose you want the audit data to be logged even if the primary transaction fails (that is, the trigger operation is still valid). This can be achieved through autonomous transactions.

Grammar:

TRIGGER on table_nameDECLARE xx; PRAGMA autonomous_transaction; BEGINCOMMIT;  END;       

PRAGMA autonomous_transaction; and Commmit; The statement is required.

Replace trigger (INSTEAD of Trigger)

Replace trigger definition in view

REPLACE on table_nameBEGIN xxxx;  END;    

If a delete operation is performed on a view, but there is an integrity constraint, the data of the constraint table can be deleted through the instead OF trigger.

Oracle: Triggers, autonomous transaction trigger