--======= ============
-- PL/SQL , 。
--
--====== =============
-- DML ,INSTEAD OF , 。
---DML : ORACLE DML ,
-- DML , 。
---INSTEAD OF : ORACLE , , DML ,
-- DML, INSTEAD OF 。
--INSTEAD OF DML 。
---
-- ORACLE , ORACLE .
-- , , 。
--============ =========
-- DDL , 、
-- DML , 、
-- 、
-- DML
--
--=======DML ==================
--
-- before,after
-- DML insert,update,delete
-- statement,row
-- PLSQL
--======DML ===========
--
-- (for each row)
-- : , DML 1 ,
-- , .
--=========DML ===============
-- ( )
---BEFORE
------BEFORE
------AFTER
-- AFTER
--. ( , )
--BEFORE
------BEFORE
------AFTER
----------BEFORE
----------AFTER
--AFTER
--==
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
-- , DML
{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] --
BEGIN
trigger_body;
END;
--===== 、 ,( )
--- NEW OLD
--- :old
--- :new
---============================================
-- :old.id :new.id
--insert :old.id null id
--update update update
--delete delete null
-- : sequence ( )
-- :
create table t1 (id number(4) ,c1 number(4));
create sequence s;-- s
create or replace trigger get_pk
-- insert , t1
before insert on t1 for each row --
declare
begin
-- :new , , 。
-- before insert , , :new , 。
select s.nextval into :new.id from dual;
dbms_output.put_line(':new.id'|| :new.id);-- 1,2,3,4,
dbms_output.put_line(' :old.id'|| :old.id); --
end;
-- get_pk .
insert into t1 values (100,100);
insert into t1 values (100,200);
insert into t1 values (100,300);
-- :select * from t1 ;
-- id c1
-- 1 100
-- 2 200
-- 3 300
--==== update :new , :old
create or replace trigger up_pk
before update on t1 for each row
declare
begin
dbms_output.put_line('update :new .c1'|| :new.c1);--400
dbms_output.put_line('update :old.c1'|| :old.c1); -- 300
end;
-- update: up_pk
update t1 set c1 = 400 where id = 3;
-- :new.c1 400 , :old.c1 300
--==== delete
create or replace trigger de_pk
before delete on t1 for each row
declare
begin
dbms_output.put_line('delete :new .c1'|| :new.c1);--
dbms_output.put_line('delete :old.c1'|| :old.c1);--400
end;
-- delete: de_pk
delete from t1 where id = 3;
-- :new.c1 , :old.c1 400
--========= t1 .
-- c2 , c3
create table t_log (c1 varchar2(30),c2 number(4),c3 number(4) , c4 date);
-- t1
-- for each row : update 1 .update 2 2
create or replace trigger update_log
before update on t1 for each row
-- before update on t1 , :old.c1, :new.c1
declare
begin
insert into t_log values (' Before update on t1 ',:old.c1, :new.c1 , sysdate);
end;
-- , ,
create table t_log (c1 varchar2(30),c4 date);
-- t1
-- update 1 1 ,update 2 1
create or replace trigger update_log
-- before update on t1 for each row
before update on t1
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;
--== when
create or replace trigger update_log
before update on t1 for each row
when ( old.id = 1) --
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;
-- == of
create or replace trigger update_log
before update on t1 of id for each row -- id update
when ( old.id = 1) --
declare
begin
insert into t_log values (' Before update on t1', sysdate);
end;
-- == , , DML
--
CREATE OR REPLACE TRIGGER update_cascade
AFTER DELETE OR UPDATE
OF deptno ON dept FOR EACH ROW
BEGIN
-- updaing : true , false
-- :old.deptno<>:new.deptno :
IF (UPDATING AND :old.deptno<>:new.deptno) THEN
UPDATE emp SET deptno=:new.deptno WHERE deptno=:old.deptno;
END IF;
-- deleting : true , false;
IF DELETING THEN
DELETE FROM emp WHERE deptno=:old.deptno;
END IF;
END;
-- ,
select trigger_name,status from user_triggers where table_name='UPDATE_CASCADE';
--
select line,text from user_source where name='TR_DEL_UPD_DEPTNO';
--
-- , DML ,
-- (alter trigger trigger_name disable)
alter trigger tr_emp_sal disable;
--
-- (alter trigger trigger_name enable)
alter trigger tr_emp_sal enable;
-- 、
alter table emp disable all triggers;
alter table emp enable all triggers;
--
alter trigger tr_emp_sal compile;
--
drop trigger tr_emp_sal;