"Database" Oracle Trigger Foundation

First, Oracle trigger basic Syntax

CREATE[ORREPLACE]TRIGGERTrigger_name{before | After} {INSERT|DELETE|UPDATE[ of column[,column...]]} [OR{INSERT|DELETE|UPDATE[ of column[,column...]]} ...] on[Schema.] table_name | [Schema.] view_name[referencing {old [ as] old | NEW [ as] New| PARENT asparent}][ forEach ROW [ whenCondition]pl/sql_block | Call procedure_name;

Before: Executes the trigger content before the trigger event is executed;

After: Executes the trigger content after the trigger event is executed;

(There seems to be a 3rd: instead of, mainly used for views.) To learn .... ）

For each row: a row-level trigger, which corresponds to a statement-level trigger.

A row-level trigger that executes 1 triggers as long as the data row matches the trigger condition;

Statement-level triggers, where the entire statement operation acts as a trigger event and matches the trigger condition, only 1 triggers are executed; (this is not a deep understanding)

New/old: only exists with the for each row. and Update:new, Old,insert:new,delete:old;

Second, the basic demo

Description: When updating the child table, the name of the parent table is spliced with "123".

Create orReplaceTriggerTest_triggerbeforeUpdate  on" Child" forEach rowDeclareOld_age number;  New_age number;  V_ID number; P_ID number;--pragma autonomous_transaction; -Autonomous transaction, independent of the transaction triggering the SQLbeginOld_age: =: old.child_age;  New_age: =: new.child_age;  v_id: =: new.child_id;  P_ID: =: new.parent_id; Dbms_output.put_line ('after:old=' | | old_age| | ', new=' | | New_age);if3>2 Then--UPDATE child set child_name = child_name| | ' 123 ' WHERE child_id = 5;UPDATEParentSetParent_name = parent_name| | '123‘whereparent_id = p_id;--:new.child_name: =: Old.child_name | | ' 123 ';Dbms_output.put_line ('if scope!‘);End if;EndTest_trigger;

Wonderful question: if the IF syntax exists in trigger,

Note the demo last end if; End  Test_trigger; If you do not write this (divided into 2 lines), this trigger in the Navicat compilation is not a pass. But it is correct in PL/SQL.

III. Trigger Affairs, self-government affairs

If the trigger does not have an autonomous transaction, then the transaction of the trigger and the transaction that triggers the trigger event are the same.

The problem I encountered during the test: 1. Purpose: after updating child_age, the name Child_name is updated by the trigger. 2, because I want to really update, only update chile_name, so I use the before.

If you do not have an autonomous transaction, then the trigger and trigger event are in the same transaction (that is, after, the transaction that triggered the event is not committed.) ), Oracle does not allow DML to be repeated on the same row of records.

However, if an autonomous transaction is used, it is obvious that if the statement that triggered the event is rolled back, then the trigger is determined to be triggered and committed. So, it's not possible to use an autonomous transaction here (I really didn't think of a scenario that would use an autonomous transaction)

How is it implemented?

At this point it is necessary to use the difference between the Before/after, by the previous know that the default trigger and trigger event is in the same transaction. You can then modify the value of the field directly in the before by before to achieve the goal : New . As shown in the demo:

: NewChild_name: = :old. child_name | | ' 123 '; This will actually update the field values for the row .

Iv. remarks

on the internet to look at the flip-flop, found that most of the ideas are not recommended triggers. Conclusion: 1, the trigger performance is not good, 2, the trigger is too covert, easy to be neglected; 3, low portability, difficult to maintain.

For performance, how much is unclear.

Portability, the items I contacted are not considered.

Concealment, which is critical if the project is poorly managed. It may not be known that there is a trigger.

In summary, I can only understand that everyone is not recommended ...

This is why I want to use the situation: there is a table table_a, there is a status bit a.status=[1,2,3,4,5,6 ...] Send SMS alerts to customers when the status bit is [4,5,6].

Existing practice: 1, in all code, change the status of the local judgment, the direct call to send text messages (or put text messages to the pool, timed to send.  It's a big one, but it's not. 2, timer, detection status bit, and then send. 3, Trigger.

Mode 1: I feel stupid, you have to find all the places now. If you add a new place to change status, people don't necessarily know to send text messages. (and it's not that simple, in case this update is rolled back, do you send SMS without avalanche?) If it is put in the SMS pool is OK, all rolled back. ）

Mode 2: There is a problem, you need to know whether this line of data is changed, and has not sent a text message (trouble, I did not think about this)

Mode 3: Trigger, detect the status of the line, then match the insert one text message to the SMS pool, and then send the SMS in the timed heartbeat message pool.

Appendix

[Oracle official documentation 12c]create TRIGGER Statement

ORACLE PL/SQL Programming Eight: Flip the trigger.

"Database" Oracle Trigger Foundation