You Asked

Hi,

Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit

We have a need to keep certain values in a table constant in a cloned database.

We store email addresses in a table, and use these for generating email communications. In a cloned environment we update all of these emails to a test email address [campus.test@domain.ac.uk], so we don't accidentally send out emails to students when testing.

The problem comes when people are testing things concurrently : One person is testing, say, inserting new students, and another person is testing the communication process. It is possible that the first person will insert or update a student's email address, thereby making it "Live", and the second person uses this email address by mistake - and hey presto a test communication is sent to a real student.

Note : The communication process does not keep a "consistent view" of the data, and can take many hours to run; so between starting the process and sending the emails out, there is plenty of opportunity for changes to occur.

Is there any way to "glue" the value in the email column - making it impossible to change this, but allowing other columns in the table to be altered ? We have looked at using triggers, but the after update trigger doesn't seem to allow the :OLD and :NEW values to be altered, and I imagine it would start an update cascade anyway with a never-ending update trigger firing ? The before-update looks like it might work, but will add overheads and complexity.

But my question is really - are triggers the only way the approach this, is there some magic in 12c ?

Thanks
Andrew

and Chris said...

You can define a trigger that only fires if you're changing a particular column. So you could use this to raise an exception whenever someone tries to change the email address:

create table t (
  c1    int,
  email varchar2(100),
  stuff varchar2(100)
);

insert into t values ( 1, 'test@test.com', 'blah' );
commit;

create or replace trigger trg 
before update of email on t
for each row
begin
  raise_application_error ( 
    -20001, q'|You can't change the email address!|' 
  );
end;
/

update t
set    email = 'new@test.com';

ORA-20001: You can't change the email address!

update t
set    stuff = 'other stuff';

select * from t;

C1   EMAIL           STUFF         
   1 test@test.com   other stuff

Of course, if you're sure you want all rows to have the same value, I'd create a check constraint:

drop trigger trg;
alter table t 
  add constraint email_c
  check ( email = 'test@test.com' );
  
update t
set    email = 'new@test.com';

ORA-02290: check constraint (CHRIS.EMAIL_C) violated

update t
set    stuff = 'different stuff';

select * from t;

C1   EMAIL           STUFF             
   1 test@test.com   different stuff

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Andrew, February 06, 2019 - 7:46 am UTC

Thanks for that :)

If we wanted a way of allowing the change to happen (not triggering the constraint) because that process will fail if it hits a constraint, but then immediately rolling it back so the communication process can complete with the test email address - is that possible ?

Andrew

February 06, 2019 - 9:24 am UTC

Sounds like you want to have your cake and eat it ;)

Can't the testers use the dummy email address?

Depending on how your process works, deferrable constraints might help. This will delay enforcing the check constraint until commit. So DML after it will continue and you'll only get the exception at the end:

create table t (
  c1    int,
  email varchar2(100),
  stuff varchar2(100)
);

insert into t values ( 1, 'test@test.com', 'blah' );
commit;

alter table t 
  add constraint email_c
  check ( email = 'test@test.com' )
  deferrable initially deferred;
  
update t
set    email = 'new@test.com';

select * from t;

C1   EMAIL          STUFF   
   1 new@test.com   blah   

commit;

ORA-02091: transaction rolled back
ORA-02290: check constraint (CHRIS.EMAIL_C) violated

select * from t;

C1   EMAIL           STUFF   
   1 test@test.com   blah

But if you want testers to be able to insert real email addresses, save, and view this change AND prevent the email job picking these real addresses up there's no simple solution I'm aware of. You should rethink your testing processes.

Andrew Mcguiness, February 06, 2019 - 9:58 am UTC

Thanks for that - it might work.

If not, we might look at creating a view of the table, with a dummy, defaulted, column for the email address, and get the communication process to use the view rather than the table - this moves the problem from the database to the application - which is always good in my book :)

And yes, both owning and consuming cake are high priorities here.

Many thanks
Andrew

February 06, 2019 - 10:19 am UTC

:)

Yep, a view with a dummy value for email address for the communication process is probably the way to go.

Or - if you're lucky enough to have the Advanced Security Option - you could use Data Redaction:

create table t (
  c1    int,
  email varchar2(100),
  stuff varchar2(100)
);

insert into t values ( 1, 'student1@test.com', 'blah' );
insert into t values ( 2, 'student2@test.com', 'blah' );
commit;

select * from t;

C1   EMAIL               STUFF   
   1 student1@test.com   blah    
   2 student2@test.com   blah   

begin
  dbms_redact.add_policy(
    object_schema => user,
    object_name   => 't',
    column_name   => 'email',
    policy_name   => 'redact_email',
    function_type => dbms_redact.regexp,
    regexp_pattern => '.*@.*',
    regexp_replace_string => 'test@test.com',
    regexp_occurrence => dbms_redact.re_all,
    expression    => '1=1'
  );
end;
/
grant create session to u identified by u;
grant select on t to u;

conn u/u

select * from chris.t;

C1   EMAIL           STUFF   
   1 test@test.com   blah    
   2 test@test.com   blah

Note the policy doesn't apply if your user has the "EXEMPT REDACTION POLICY" privilege. Which the DBA role has.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs

Keeping a column constant

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

More to Explore

PL/SQL demos

PL/SQL docs