时间戳

首页 > 解决方案 > 如何在某些条件下创建更新触发器？

sql - 如何在某些条件下创建更新触发器？

问题描述

我想创建一个触发器，该触发器强制要求声明为详细信息列的条件只能在放置后 3 天内更改

CREATE TRIGGER BEFORE UPDATE

CREATE TRIGGER INSTEAD OF UPDATE

我有OrderId, Details,OrderDate列

我怎样才能做到这一点？你能帮忙吗，请

标签： sqloracleoracle11gdatabase-trigger

解决方案

这是一个例子。

一、测试用例：

SQL> alter session set nls_date_format = 'dd.mm.yyyy';

Session altered.

SQL> create table test
  2    (orderid   number primary key,
  3     details   varchar2(20),
  4     orderdate date
  5    );

Table created.

SQL> insert into test (orderid, details, orderdate)
  2    select 1, 'test 1', date '2018-10-15' from dual union all
  3    select 2, 'test 2', date '2018-10-22' from dual;

2 rows created.

SQL> select sysdate from dual;

SYSDATE
----------
23.10.2018

SQL> select * from test;

   ORDERID DETAILS              ORDERDATE
---------- -------------------- ----------
         1 test 1               15.10.2018
         2 test 2               22.10.2018

SQL>

扳机：

SQL> create or replace trigger trg_bu_test
  2    before update of details on test
  3    for each row
  4  begin
  5    if trunc(sysdate) - :new.orderdate > 3 then
  6       raise_application_error(-20001, 'More than 3 days have passed; update is not allowed');
  7    end if;
  8  end;
  9  /

Trigger created.

测试：

SQL> -- ID = 1 - 8 days have passed, no update is allowed
SQL> update test set details = 'xxx' where orderid = 1;
update test set details = 'xxx' where orderid = 1
       *
ERROR at line 1:
ORA-20001: More than 3 days have passed; update is not allowed
ORA-06512: at "SCOTT.TRG_BU_TEST", line 3
ORA-04088: error during execution of trigger 'SCOTT.TRG_BU_TEST'


SQL> -- ID = 2 - 1 day passed - update is allowed
SQL> update test set details = 'yyy' where orderid = 2;

1 row updated.

SQL>

PSINSTEAD OF触发器（您在问题中提到）与视图一起使用；忘记他们（在这种情况下）。

推荐阅读