oracle触发器练习（Oracle flip flop exercise）.docVIP

oracle触发器练习（Oracle flip flop exercise）DML flip flop:Exciting when performing Insert, Update, and Delete operations on the tableIt can be used to perform verification, set initial values, audit changes, and even prohibit certain DML operationsGrammar:CREATE OR REPLACE TRIGGER trigger name{AFTER|BEFORE} specifies the trigger time{INSERT OR DELETE OR UPDATE} - specifies trigger eventsON table name - specifies the table to be monitored{FOR EACH ROW|FOR EACH STATEMENT} - specifies the number of triggersBEGIN-- code;END;Related concepts:AFTER|BEFORE: before or after an eventINSERT|DELETE|UPDATE: what event?ON table name: what table does the trigger build on? What table does it monitor?FOR EACH ROW: row level trigger, example: delete from T1, delete the 1000 line, then execute 1000 times (one line, one time)FOR EACH STATEMENT: statement level trigger, example: delete from T1, delete the 1000 line, then execute 1 times (once a sentence)New: variable: save the event new row, only the insert and update events have new dataOld row variables: save the old data when the event occurs, only the delete event and the update event have the old dataExample: points out new data and old data in events and eventsInsert into EMP (empno, ename) values (51,job);Analysis: there is only one line of new data (51, job), corresponding to: new variable.Update EMP set ename=oraclewhere empno = 51;Analysis: old data (51, job), corresponding: old variable.New data (51, Oracle), corresponding: new variable.Delete from EMP where empno = 51;Analysis: there is only one row of old data (51, Oracle), corresponding to: old variable.Sql codeDisplay triggerSelect trigger_name, status from user_triggers;Prohibit triggersAlter trigger tr_emp_salary disable;Activate triggersAlter trigger tr_emp_salary enable;-- all triggers that prohibit the tableAlter table employee disable all triggers;Activate all triggers of the tableAlter table employee enable all triggers;Recompile triggers--alter table tr_emp_salary comp