我要用触发器实现每天下班时间不能访问表:
create or replace trigger hospital14_dui
before insert or update or delete on hospital14
declare
v_time varchar2(20);
begin
v_time:=to_char(sysdate,'hh24:mi:ss');
if (v_time>17:00:00 or v_time<8:00:00)
then
raise_application_error(-2000,'a table can not be modified');
end if;
end;结果编译出错,是不是 if (v_time>17:00:00 or v_time<8:00:00)
这句写错了?该怎么改呢?
create or replace trigger hospital14_dui
before insert or update or delete on hospital14
declare
v_time varchar2(20);
begin
v_time:=to_char(sysdate,'hh24:mi:ss');
if (v_time>17:00:00 or v_time<8:00:00)
then
raise_application_error(-2000,'a table can not be modified');
end if;
end;结果编译出错,是不是 if (v_time>17:00:00 or v_time<8:00:00)
这句写错了?该怎么改呢?
还有一个地方,错误号码范围不对,自定义错误只能用-20001~-20999之间的数字
create or replace trigger hospital14_dui
before insert or update or delete on hospital14
declare
v_time varchar2(20);
begin
v_time:=to_char(sysdate,'hh24:mi:ss');
if (v_time>'17:00:00' or v_time<'8:00:00')
then
raise_application_error(-20001,'a table can not be modified');
end if;
end;
before insert or update or delete on hospital14
declare
v_time varchar2(20);
begin
v_time:=to_char(sysdate,'hh24:mi:ss');
if (v_time>'17:00:00' or v_time<'8:00:00')
then
raise_application_error(-2000,'a table can not be modified');
end if;
end
少了引号。
麻烦帮我再看下,改好好像也不行。
SQL> edit;
已写入 file afiedt.buf 1 create or replace trigger hospital14_dui
2 before insert or update or delete on hospital14
3 declare
4 v_time varchar2(20);
5 begin
6 v_time:=to_char(sysdate,'hh24:mi:ss');
7 if (v_time>'17:00:00' or v_time<'8:00:00')
8 then
9 raise_application_error(-2000,'a table can not be modified');
10 end if;
11* end;
SQL> /触发器已创建SQL> delete from hospital14;
delete from hospital14
*
第 1 行出现错误:
ORA-21000: -2000 的 raise_application_error 错误号参数超出范围
ORA-06512: 在 "ZLR.HOSPITAL14_DUI", line 7
ORA-04088: 触发器 'ZLR.HOSPITAL14_DUI' 执行过程中出错
SQL> select to_char(sysdate,'hh24:mi:ss') from dual;TO_CHAR(
--------
15:17:42SQL>按理说现在是可以访问表的啊?怎么也不能访问呢?现在时间是15:17:42
SQL> create or replace trigger hospital14_dui
2 before insert or update or delete on hospital14
3 declare
4 v_time varchar2(20);
5 begin
6 v_time:=to_char(sysdate,'hh24:mi:ss');
7 if (v_time>'17:00:00' or v_time<'8:00:00')
8 then
9 raise_application_error(-20001,'a table can not be modified');
10 end if;
11 end;
12 /触发器已创建SQL> delete from hospital14;
delete from hospital14
*
第 1 行出现错误:
ORA-20001: a table can not be modified
ORA-06512: 在 "ZLR.HOSPITAL14_DUI", line 7
ORA-04088: 触发器 'ZLR.HOSPITAL14_DUI' 执行过程中出错
create or replace trigger hospital14_dui
before insert or update or delete on hospital14
when (to_char(sysdate,'hh24') < '08' or to_char(sysdate, 'hh24') > '17') --每天 8 点以前和 17 点以后不得操作表
declare
begin
raise_application_error(-20001,'a table can not be modified');
end;
/
create or replace trigger hospital14_dui
before insert or update or delete on hospital14
declare
v_time varchar2(20);
begin
v_time:=to_char(sysdate,'hh24:mi:ss');
if (v_time>'17:00:00' or v_time<'08:00:00')
then
raise_application_error(-20001,'a table can not be modified');
end if;
end;
代码是灵活的呀。。