Triggers, oracle triggers

Triggers, oracle triggers
Directory

I. DML triggers

Ii. INSTEAD_OF trigger

Iii. system triggers

4. Delete trigger modify trigger status close open

-- Description -- 1. BEFORE the event (BEFORE) AFTER the event (AFTER) -- 2. trigger Condition Clause WHEN--3. STATEMENT-level (STATEMENT) Trigger and ROW-level (ROW) trigger -- 3.1 STATEMENT: When a trigger event occurs, the trigger is executed only once; -- 3.2 ROW: when a trigger event occurs, the trigger is executed independently for each row of data affected by the operation. -- 4. Only DML statements (SELECT, INSERT, UPDATE, and DELETE) can be used for trigger execution, and DDL statements (CREATE, ALTER, and DROP) cannot be used ).

 

I. DML triggers

-- Create an empty table record with the same structure as the emp table and delete the log create table emp_del as select * from emp where 1 = 2; -- 1. create DML trigger [row-level delete trigger] create or replace trigger tr_del_emp before delete -- specify to trigger on emp for each row for deletion -- indicates that the row-Level trigger begin insert into emp_del (deptno, empno) values (: old. deptno,: old. empno); end; -- delete emp t where t. empno = '000000'; -- Note: the value of the column after the NEW modifier access is completed: the value of the oldest modifier before the access is completed

-- 2. restrict DML trigger create or replace trigger tr_dept_time before insert or delete or update on deptbegin if (true) then -- write the custom condition RAISE_APPLICATION_ERROR (-20001, TO_CHAR (sysdate, 'day') | 'the dept table cannot be modified'); -- custom exception message end if; end; delete dept t where t. deptno = '10 ';

-- 3 modify the field ename, job, and DELETE the dml trigger create or replace trigger tr_emp_sal_comm before update of ename, job or delete on emp -- modify the field ename, job and delete trigger for each row when (old. empno = '000000') -- the old and new clauses in the when clause cannot add ":". In the PL/SQL BLOCK statement, the new: old clause must be added. Begin case when updating ('ename') then if: new. ename! =: Old. ename THEN/*: new. ename! =: Old. here, we can use the new and old values for logical judgment. When the value is null, the condition constant is false */RAISE_APPLICATION_ERROR (-20001, 'enpno is 7369, And the ename cannot be modified '); elsif: new. ename =: old. ename THEN RAISE_APPLICATION_ERROR (-20001, 'ename cannot be changed '); else dbms_output.put_line (: new. ename | ''|: old. ename); RAISE_APPLICATION_ERROR (-20001, 'ename cannot be changed in other cases '); end if; when updating ('job') then if: NEW. job! =: Old. job THEN RAISE_APPLICATION_ERROR (-20002, 'job with enpno 7369 cannot be modified '); end if; when deleting then RAISE_APPLICATION_ERROR (-20003, 'enpno 7369 cannot be deleted '); end case; END; -- 7499 7566 select * from emp t where t. empno = 7499; delete emp t where t. empno = 7499; update emp t set t. job = '000000' where t. empno = '000000'; update emp t set t. ename = '000000' where t. empno = '000000 ';

Ii. INSTEAD_OF trigger

-- Create or replace view my_ceshi_view as select t. empno from emp t group by t. empno; -- create an INSTEAD_OF trigger to delete create or replace trigger del_ceshi_view instead of delete on my_ceshi_view for each rowbegin delete from emp where empno =: old. empno; end; -- delete my_ceshi_view t where t. EMPNO = '000000 ';

Iii. system triggers

-- Create table ddl_event (crt_date timestamp primary key, event_name VARCHAR2 (20), user_name VARCHAR2 (10), obj_type VARCHAR2 (20 ), obj_name VARCHAR2 (20); -- CREATE System Event ddl trigger create or replace trigger tr_ddl after ddl on schemabegin insert into ddl_event VALUES (systimestamp, ora_sysevent, ora_login_user, role, role ); END tr_ddl; create table emp_bak as select * from emp; select * from ddl_event;

4. Delete trigger modify trigger status close open

-- Delete TRIGGER modify TRIGGER state close open drop trigger trigger_name; alter trigger trigger_name [DISABLE | ENABLE]; -- ENABLE -- disable alter trigger tr_del_emp DISABLE;