Pl sql trigger inserting deleting updating

A trigger is a pl/sql block structure which is fired when a DML statements like Insert, Delete, Update is executed on a database table.

pl sql trigger inserting deleting updating-23

PUT_LINE(SQLCODE||'---'||SQLERRM); END; BEGIN DBMS_OUTPUT. PUT_LINE('插入前'); Disp_dept_summary(); DBMS_UTILITY. EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_update AFTER UPDATE OF sal ON emp REFERENCING OLD AS old_emp NEW AS new_emp FOR EACH ROW WHEN (old_! PUT_LINE(''正在执行trig2_insert 触发器…''); SELECT COUNT(*) INTO I FROM dept_summary WHERE deptno = :new_emp.deptno; IF I 0 THEN UPDATE dept_summary SET sal_sum=sal_sum :new_emp.sal, Emp_count=emp_count 1 WHERE deptno = :new_emp.deptno; ELSE INSERT INTO dept_summary VALUES (:new_emp.deptno, :new_emp.sal, 1); END IF; END;' ); DBMS_UTILITY.

EXEC_DDL_STATEMENT( 'CREATE OR REPLACE TRIGGER trig2_delete AFTER DELETE ON emp REFERENCING OLD AS old_emp FOR EACH ROW DECLARE I NUMBER; BEGIN DBMS_OUTPUT.

Un trigger può essere richiamato prima (before) o dopo (after) l'evento che causa l'attivazione del trigger.

Al fine di programmare i trigger efficientemente (e correttamente), è essenziale capire la differenza tra trigger a livello di riga e trigger a livello di istruzione.

BEFORE INSERT BEFORE INSERT FOR EACH ROW AFTER INSERT AFTER INSERT FOR EACH ROW BEFORE UPDATE BEFORE UPDATE FOR EACH ROW AFTER UPDATE AFTER UPDATE FOR EACH ROW BEFORE DELETE BEFORE DELETE FOR EACH ROW AFTER DELETE AFTER DELETE FOR EACH ROW CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; CREATE OR REPLACE TRIGGER tr_del_emp BEFORE DELETE --指定触发时机为删除操作前触发 ON FOR EACH ROW --说明创建的是行级触发器 BEGIN --将修改前数据插入到日志记录表 del_emp ,以供监督使用。 INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate ) VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate ); END; DELETE emp WHERE empno=7788; DROP TABLE emp_his; DROP TRIGGER del_emp; CREATE OR REPLACE TRIGGER tr_dept_time BEFORE INSERT OR DELETE OR UPDATE ON departments BEGIN IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24: MI') NOT BETWEEN '' AND '') THEN RAISE_APPLICATION_ERROR(-20001, '不是上班时间,不能修改departments表'); END IF; END; CREATE OR REPLACE TRIGGER tr_emp_sal_comm BEFORE UPDATE OF salary, commission_pct OR DELETE ON HR.employees FOR EACH ROW WHEN (old.department_id = 80) BEGIN CASE WHEN UPDATING ('salary') THEN IF : NEW.salary CREATE OR REPLACE TRIGGER tr_reg_cou AFTER update OF region_id ON regions FOR EACH ROW BEGIN DBMS_OUTPUT.

Last modified 23-Apr-2020 17:22