触发器用于控制用户插入.txt

来自「orale培训教材包括了所有的sql说明和实例」· 文本 代码 · 共 53 行

--考虑控制userenv()代表的终端。只许一个终端插入。
create or replace trigger test
before insert or update or delete
on sm_emp
for each row
declare 
       users varchar2(10);
begin
       --select user into users from dual;
	users :=user;
       if users = 'SCOTT' then
             raise_application_error( -20005,' illegal user  ');
       end if;
end;

create or replace trigger test
before insert or update or delete
on sm_emp
for each row
declare 
       users varchar2(10);
begin
       --select user into users from dual;
	users :=userenv('terminal');
       if users != 'A1038' then
             -- 输出提示
             raise_application_error( -20005,' illegal PC');
       end if;
       --log 输出
end;

---------------------------------扩展-------
create or replace trigger test
before insert or update or delete
on sm_emp
for each row
declare 
       users varchar2(10);
       ok_pc varchar2(1);
begin
       --select user into users from dual;
	users :=userenv('terminal');
	--定义函数检查users对应的pc是否在允许插入的列表内
	ok_pc :=调用函数();
	--ok_pc= 0为不在允许插入的列表内
       if ok_pc= 0 then
             --log 输出
             raise_application_error( -20005,' illegal PC');
       end if;
       --log 输出
end;

insert into sm_emp values('oo',9,9,9);