Oracle trigger (1): General Usage

Oracle trigger (1): Generally, trigger is similar to procedure and function, but it cannot be displayed and called. It can only be triggered by an event and then automatically called by oracle. generally, you can create a trigger for a table or view and trigger a trigger when you perform some operations on the table or view. of course, there are schema and database-level triggers. common triggers for operations are DML (insert, update, delete) and DDL (create, alter, drop) statements. A schema-Level trigger is triggered when the session is connected or disconnected. database-Level trigger is triggered when the system starts or exits. you may easily find that trigger cannot be used for select query operations, but sometimes we want to monitor who has checked some sensitive information. At this time, we can only use one thing called FGA, you can create an audit policy (which can be viewed as an enhanced trigger. For more information about FGA, see: http://www.bkjia.com/database/201112/115697.html ) Trigger usage considerations 1. the trigger does not accept parameters. A table can have up to 12 triggers (the trigger type is exactly 12), and at the same time, the same event, only one trigger of the same type can be created ). 2. the maximum trigger size is 32 KB. Because of the limited size, long and blob variables cannot be used. if there is really a complicated logic, to get a complicated trigger, you can use procedure or function to implement some functions and then call 3. because the trigger can be seen as part of the trigger statement. therefore, some constraints must be observed, such as the absence of transaction control statements (commit, rollback, savepoint) and DDL statements. why? The major difference between these special statements and general SQL statements involves the issue of commit. therefore, if the trigger statement is only a general statement, it cannot be because the trigger operation has the commit feature. the trigger format for creating a trigger for a table OR view is as follows: CREATE [or replace] TR IGGER trigger_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 as parent}] [for each row] [WHEN condition] PL/SQL _BLOCK | CALL procedure_name; let's take a look at how to create a trigger at the statement level. if there is a table tb1, each insert point records some information in the tblog through the trigger. create or replace trigger tb1_trigger after insert on tb1 referencing new as new old as old declare v_info varchar2 (100); begin v_info: = "do a insert"; insert into tblog (info) values (v_info); end; the trigger creation contains something that is not easy to understand: A new value of the row-level trigger. The old value of the row-level trigger. The new value is a trigger at the statement level and row level when a trigger is created for a table or view. the statement level means that an SQL statement triggers a trigger once. If the row level means that an SQL statement involves multiple rows of data, the trigger is triggered multiple times. the old value refers to the value of the row of data to be changed before it is changed. The new value refers to the value after the user updates it. assume that the tt table has only one row of data: 88. then the user executes the statement update tt set id = 99 where id = 88; then the old value is 88, and the new value is 99. then you may ask how to get the old value or new value. for example, if the table tb (eno int); and the table tblog (info varchar2 (100); If the trigger is created on the tb, once every tb update, the new value after the change is recorded in the tblog. create or replace trigger tb_trigger before update on tb referencing new as new_val old as old_val -- set the name here and then reference the NEW value. The OLD value is for each row declare v_info varchar2 (100 ); BEGIN v_info: = 'old value: '| to_char (: old_val.eno) | 'new value:' | to_char (: new_val.eno); insert into tblog values (v_info ); END; condition judgment if trigger is triggered only when a row of operations are involved, if the trigger is triggered for updat, delete, and insert. what about it? Naturally, we use when to judge it. for example, create or replace trigger tb_trigger before update or insert or delete ON tb referencing new as new_val old as old_val -- set the name here and then reference the NEW value, old value for each row when (old_val.eno = 99) DECLARE v_info varchar2 (100); BEGIN case when updating then v_info: = 'old value: '| to_char (: old_val.eno) | 'new value: '| to_char (: new_val.eno); insert into tblog values (v_info); when inserting then null; when deleting then null; end case; END;