语句触发器PL/SQL

在这里,我住的地方,休息时间也是周六&周日(即周末),所以-我建议如下:

SQL> create table purchase_stock (stock_id number, quantity number);

Table created.

SQL> create or replace trigger trg_biu_pursto
  2    before insert or update on purchase_stock
  3  declare
  4    -- day number (1 = Monday, 2 = Tuesday, ..., 7 = Sunday)
  5    l_day  number := to_number(to_char(sysdate, 'd'));
  6    -- current hour (e.g. now is 13:45 -> l_hour = 13)
  7    l_hour number := to_number(to_char(sysdate, 'hh24'));
  8  begin
  9    if l_day in (6, 7) or
 10       l_hour not between 9 and 17
 11    then
 12       raise_application_error(-20000, 'Non-working time; table not available');
 13    end if;
 14  end;
 15  /

Trigger created.

SQL> insert into purchase_stock values (1, 2);
insert into purchase_stock values (1, 2)
            *
ERROR at line 1:
ORA-20000: Non-working time; table not available
ORA-06512: at "SCOTT.TRG_BIU_PURSTO", line 10
ORA-04088: error during execution of trigger 'SCOTT.TRG_BIU_PURSTO'


SQL>

假设您的办公时间在08:00到17:00之间,您可以在下面创建这样一个触发器,以防止在您可以使用的办公时间之外更新数据 raise_application_error 声明:

CREATE OR REPLACE TRIGGER trg_upd_PrcStock
BEFORE UPDATE ON PurchaseStock 
FOR EACH ROW

DECLARE
   v_hour pls_integer;    
BEGIN
  select into v_hour extract(hour from cast(sysdate as timestamp)) from dual;

  IF v_hour between 8 and 16 THEN 
   -- trigger code    
  ELSE
   raise_application_error('-20333','You can not perform update out of office hours!');
  END IF;
END;