oracle 触发器

 

对象类型

create or replace type obj1 as object(
	in number(9),
        name varchar2(20)
)

declare
 o obj1;
begin
 o:=obj1(1,'zhangsan');
 dbms_output.put_line(o.id||o.name);
end;

触发器

触发事件
触发条件
触发对象
触发操作

DML
instead of
系统

1.没有参数
2.12个触发器（一个时间点的触发器只能有一个）
3.32k（触发器不能超过32k，纯文字）
4.触发器的执行部分，只能执行dml，不能执行ddl（no）
5.不能包含 commit rollback

DML触发器
 语句级触发器：以语句为单位
 行级触发器：以行为单位

语句触发器：
creat or replace trigger triggername
before|after 
insert|delete|update[or]
on tablename
for each row
[when condition]
pl/sql block[begin...end]


select * from myemp;
非工作时间，禁止对myemp表进行dml操作。
update myemp set sal=sal+100 where empno=7369

old 变化前
new 变化后

insert old null new 
delete old      new null
update

create table log(
  id number primary key,
  empno number(7,2),
  sal number(7,2),
  dt date,
  status number(1)
)

select * from log

create seqquence seq;

create or replace trigger tri_emp
after update on myemp
for each row

begin
  if(:new.sal >1000) then
   insert into log values(seq.nextval,:new.empno,:new.sal,sysdate,1);
  else
 insert into log values(seq.nextval,:new.empno,:new.sal,sysdate,0)
  end if;
end;


创建触发器

create or replace trigger myemp_time
 before insert or update or delete
on myemp
begin
 
if(to_char(sysdate,'DAY') in ('星期六','星期日')) or to_char(sysdate,'HH24:MI')
then
raise_application_error(-20010,'not working time,can not modify the table named myemp!!!')
end if;
end;

show errors trigger myemp_time;


select to_char(sysdate,'DAY') from daul

if(to_char(sysdate,'DAY') in ('星期六','星期日')) or to_char(sysdate,'HH24:MI')

raise_application_error(-10000,'not working time,can not modify the table named myemp!!!')
end if;

select to_char(sysdate,'HH24:MI') from dual not between '9:00' and '17:00')

行级触发器

--20部门的工资不能降低，也不能删除20号部门的人员
create or replace trigger tr_myemp
before
update or delete
on myemp
for each row
when (old.deptno=20)
begin
  case 
    when updating ('sal') then 
       if :new.sal<:old.sal then
         raise_application_error(-20002,'the sal of deptno 20 can be down!!!')
         end if;
      when deleting then
          raise_application_error(-20002,'the sal of deptno 20 can be deleted!!!')
    end case;
end;

update myemp set sal=sal-100 where empno=7369;
update myemp set sal=sal-100 where empno=7782;

系统触发器
DDL
 启动关闭数据库 登录
create or replace trigger name
before after
ddl_event_list | datebase_event_list
on {datebase |schema}
ps/sql block

create drop alter grant revoke audit logon logoff startup shutdown servererror

create table ddl_event(
   dt timestamp primary key,
   event_name varchar2(20),
   user_name varchar2(20),
   obj_type varchar2(20),
   obj_name varchar2(20),
)

create or replace  trigger tr1_ddl
after DDL on schema
begin
   insert inot ddl_event values(
	systimestamp,ora_sysevent,
        ora_datebase_name,
	ora_dict_obj_type,
	ora_dict_obj_name)
end;

常用属性函数(触发器)
ora_login_user
ora_sysevent
ora_client_ip_address
ora_datebase_name
ora_dict_obj_type
ora_dict_obj_name
ora_is_servererror

alert table t2 modify (id varchar2(20));

create table logon_event(
	username varchar2(20),
        ipaddress varchar2(20),
        login_date timestamp,
        logon_date timestamp
)

select * from logon_event

create or replace trigger  tr1_logon
after logon on database
begin
  insert into logon_event(username,ipadderss,login_date)
  values(ora_login_user,ora_clint_ip_address,systimestamp);

end;

create or replace trigger  tr1_logoff
before logoff on database
begin
  insert into logon_event(username,ipadderss,logout_date)
  values(ora_login_user,ora_clint_ip_address,systimestamp);

end;

数据字典

desc user_triggers;
select * from user_triggers where trigger_name=upper('tr1_logoff');
触发器的启用和禁用
aleter trigger tr1_logoff diable[enable]
系统的包
dbms_output.put_line();