SQL> create table testx ( xsmall smallint );
Table created.
SQL> create table tracex ( id_timestamp timestamp , who_was varchar2(50) , sqltext varchar2(4000) );
Table created.
SQL> create or replace procedure pr_handle_it (object_schema VARCHAR2, object_name VARCHAR2, policy_name VARCHAR2)
is
begin
-- dbms_output.put_line('SQL was: ' || SYS_CONTEXT('userenv','CURRENT_SQL'));
insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
commit;
end;
/
Procedure created.
SQL> BEGIN
DBMS_FGA.ADD_POLICY(
object_schema => 'MYSCHEMA',
object_name => 'TESTX',
policy_name => 'MY_NEW_POLICY',
audit_condition => null,
audit_column => 'XSMALL',
handler_schema => 'MYSCHEMA',
handler_module => 'PR_HANDLE_IT',
enable => true,
statement_types => 'INSERT, UPDATE, DELETE'
);
END;
/
PL/SQL procedure successfully completed.
SQL> create or replace trigger trg_testx before insert or update on testx
referencing new as new old as old
for each row
begin
if inserting or updating
then
dbms_output.put(' New value is: ' || :new.xsmall);
dbms_output.put_line('TRIGGER : The value for CURRENT_SQL is '||sys_context('userenv','current_sql'));
insert into tracex values ( systimestamp , sys_context('userenv','session_user') , sys_context('userenv','current_sql') );
end if;
end;
/
Trigger created.
SQL> create or replace trigger trg_testx2 after insert or update on cpl_rep.testx
referencing new as new old as old
for each row
declare
v_val pls_integer;
begin
if inserting or updating
then
select regexp_replace(sqltext,'[^0-9]+','') into v_val
from ( select upper(sqltext) as sqltext from tracex order by id_timestamp desc ) where rownum = 1 ;
if v_val > 5
then
raise_application_error(-20001,'Number greater than 5 or contains decimals');
end if;
end if;
end ;
/
Trigger created.
SQL> insert into testx values ( 1 ) ;
1 row created.
SQL> insert into testx values ( 5 ) ;
1 row created.
SQL> insert into testx values ( 2.1 ) ;
insert into testx values ( 2.1 )
*
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'
SQL> insert into testx values ( 6 ) ;
insert into testx values ( 6 )
*
ERROR at line 1:
ORA-20001: Number greater than 5 or it contains decimals
ORA-06512: at "CPL_REP.TRG_TESTX2", line 10
ORA-04088: error during execution of trigger 'CPL_REP.TRG_TESTX2'
2条答案
按热度按时间nukf8bse1#
这太长了,不能发表评论。
你不能轻易地做你想做的事。oracle正在转换输入值
3.2
一个整数。整数满足约束。价值3
就是插入的东西。转换发生在幕后。甲骨文的开发者认为这种转换是一件“好事”。您可以通过将列声明为数字,然后检查它是否为整数来解决此问题:
krcsximq2#
至于要求是不改变数据类型,但它没有说明任何关于创建新对象的内容,我提出了一个非常复杂的解决方案,它可以做到这一点,但到目前为止,我更希望将数据类型改为数字并使用普通约束。
这里的主要问题是,值的取整是在解析语句之后执行之前完成的。作为一种内在机制,你不能做任何事。如果使用触发器并显示
:NEW
在插入或更新列之前。然而,有一个技巧。f、 g.a.在解析之前将原始值传递给语句。因此,使用带有一个处理程序和两个触发器的策略就可以达到目的。
让我详细谈谈
这些是要素:
-一个跟踪表,用于在解析之前获取查询
-一个fga策略覆盖更新和插入
-处理程序的一个过程
-两个触发器一个在前面(获取查询和原始值),一个在后面,用于计算语句中的值,而不是取整的值。
由于触发器是按顺序计算的,before-one插入带小数点的原始值并在取整之前执行,after分析存储在跟踪表中的值以引发异常。
总结:正如@gordon linoff所说的,要实现所要求的目标没有简单的方法。我相信这个方法对于要求来说是非常复杂的。我只是想证明这是可能的。