Original reference: http://plsql-tutorial.com/Create syntax:CREATE [OR REPLACE]TRIGGER trigger_name{before | After | INSTEAD of}{INSERT [OR] | UPDATE [OR] | DELETE}[of Col_name]On table_name[Referencing old as O NEW as n] [For each ROW] When (condition) BEGIN---SQL statementsend; = = Create Description: A long time not to translate, ========================
- create [OR REPLACE] TRIGGER t rigger_name-this clause creates a trigger with the given name or overwrites an existing trigger with the same name.
- {before | After | INSTEAD of}-this clause indicates at "What time should the trigger get fired." I.e for example:before or after Updat ing a table. INSTEAD of is used to create a trigger on a view. Before and after cannot is used to create a trigger on a view.
- {INSERT [OR] | UPDATE [OR] | DELETE} -this clause determines the triggering event. More than one triggering events can is used together separated by OR keyword. The trigger gets fired at all the specified triggering event.
- [of Col_name]-this clause was used with update triggers. This clause is used if you are want to trigger an event if a specific column is updated.
- [ON TABLE_NAME] -This clause identifies the name of the table or view to which the trigger is associated.
- - Span style= "color: #000000;" >this clause is used to reference the old and new values of the data being changed. By default, you reference the values As:old.column_name or:new.column_name. The reference names can also is changed from-old (or new) to any other user-defined name. Cannot reference old values when inserting a record, or new values when deleting a record, because they does not exist.< /span>
- [For each ROW] -this clause are used to determine whether a trigger must fire when each row gets affected (i.e. a row level Trig GER) or just once when the entire SQL statement are executed (I.e.statement level Trigger).
- When (condition)-this clause was valid only for row level triggers. The trigger is fired only for rows that satisfy the condition specified.
Example:1) Create ' product ' table and ' product_price_history ' tableCREATE TABLEproduct_price_history(product_id number (5), product_name varchar2 (+), Supplier_name varchar2 (+), Unit_price number (7,2)); CREATE TABLEProduct(product_id number (5), product_name varchar2 (+), Supplier_name varchar2 (+), Unit_price number (7,2));2) Create a Price_history_trigger trigger and executeCREATE or REPLACETRIGGER Price_history_triggerBeforeUPDATE of Unit_priceOn productFor each ROWBegininsert into product_price_historyvalues (: Old.product_id,:old.product_name,:old.supplier_name,:old.unit_ Price); end;/3) Modify the product's recordUPDATE PRODUCT SET unit_price = product_id = 1004) When the rollback operation is performed before the commit is modified, the insert operation in the trigger is also rolled backType:1) Row level trigger 2) statement level trigger
Trigger Action:DESC user_triggers; SELECT * from user_triggers WHERE trigger_name = ' trigger_name ';D rop trigger trigger_name;trigger dead Loop:1) The INSERT Trigger, triggera on table ' ABC ' issues a update on table ' XYZ '. 2] The UPDATE Trigger, triggerb on table ' X YZ ' issues an inserts on table ' ABC '.
PL/SQL Learning (vi) triggers
