玩转Oracle第7讲

 

 

 

 

 

*my_emp表可以通过 create table my_emp as select id,name,sal from emp得到。

 

create or replace trigger tr_sec_emp

before insert or update or delete on emp

begin

if to_char(sysdate,'DY','nls_date_language=american')

in ('MON','SUN') then

dbms_output.put_line(‘不能在休息日操作数据2’);-- 也可以写成 raise_application_error(-20001,'不能在休息日操作数据');

end if;

end;

/

 

create or replace trigger tr_sec_emp

before insert or update or delete on emp

begin

if to_char(sysdate,'DY','nls_date_language=american')

in ('MON','SUN') then

case

when inserting then

raise_application_error(-20001,'不能在休息日操作数据');

when updating then

raise_application_error(-20002,'不能在休息日操作数据更新');

when deleting then

raise_application_error(-20003,'不能在休息日解雇员工');

end case;

end if;

end;

/

 

②的答案

create or replace trigger tr_emp_sal

before update of sal on emp --emp 表的sal列

for each row

begin

if :new.sal<:old.sal then

raise_application_error(-20010,'工资只涨不降');

end if;

end;

 

 

•my_emp2表的数据可以从emp表得到 create table my_emp2 as select id,name,sal from emp

•精细化控制案例

create or replace trigger tr_check_sal

before update of sal on emp

for each row

when (new.sal<old.sal or new.sal>1.2*old.sal)

begin

raise_application_error(-20005,'工资在100%-120%间');

end;

 

 

*这里需要注意的事: 在建立登陆和退出触发器要用特权用户来建立，才能记录所有用户的登陆和退出情况

create or replace trigger tr_logon

after logon on database

begin

insert into log_table (username,logon_time,address)

values(ora_login_user,sysdate,ora_client_ip_address);

end;

/

 

 

create or replace trigger tr_logoff

before logoff on database

begin

insert into log_table (username,logoff_time,address)

values(ora_login_user,sysdate,ora_client_ip_address);

end;

/

 

 

*用system登陆

*创建相关的触发器来使用表

create or replace trigger tr_ddl

after ddl on scott.schema

begin

insert into event_ddl values(

ora_sysevent,ora_login_user,sysdate);

end;

/

*在建立tr_ddl后

如果在scott 方案执行了各种ddl操作，就会将信息记录到表event_ddl

conn scott/tiger

create table temp(cola int);

drop temp;

select from event_ddl;

//可以看到效果