游标 触发器 笔记

--游标的另一种用法 

--游标的另一种用法  更新的行是当前游标所指定的行

create table t1 as select ename,deptno from scott.emp;

alter table t1 add dname varchar2(20);

declare cursor c1 is select * from t1 for update;
v1 scott.dept.dname%type;
begin
for n1 in c1 loop
select dname into v1 from scott.dept where deptno=n1.deptno;
update t1 set dname=v1 where current of c1;
end loop;
end;

 

 

 

--触发器相关

select trigger_name,status from user_triggers --查询当前用户的所有触发器名称和状态

 

alter trigger {tigger_name} disable                 --使触发器无效

 

alter table   {table_name}  disable  all trigger --禁用某表上的所有触发器

 

create table d as select * from scott.dept;

create table e as select * from scott.emp;

--demo 更改 d 表 e 表做相应更改

 create or replace trigger d_dml
 after delete or update of deptno on d for each row
 begin
 if (updating and :new.deptno !=:old.deptno) then
  update e set deptno=:new.deptno where deptno=:old.deptno;
 end if;
 if (deleting) then
 delete  from e where deptno=:old.deptno;
 end if;
 end;