Updating data with an Oracle trigger

We have a requirement whereby we need to copy data from Column A to Column X in the same table via an insert / update trigger. My background isn't in Oracle so struggling a bit with syntax.

For background the copy is needed to shape data stored as unicode type - NVARCHAR and NCLOB specifically, into VARCHAR and CLOB equivalents ready for Oracle Text indexing. We cannot change the original column types, and we cannot control the INSERT / UPDATE statements as these are auto generated by an existing API.

So, let's say we have a table with existing columns including:

ColumnA NVARCHAR(100)
ColumnB NVARCHAR(300)

We append the new columns to the same table to add (and initialise with copy of source column data):

ColumnA_VAR VARCHAR(100)

We then need a trigger that can enforce the new shadow data is kept in sync with the original data. The trigger needs to handle updates to single columns, or all. ie

UPDATE SampleTable SET ColumnA = N'NewValue';
UPDATE SampleTable SET ColumnA = N'NewValue A', ColumnB = N'NewValueB'; // etc

Initial attempts have shown only one of the shadow fields update while the others were wiped, so I must stress that the trigger needs to allow editing of any number of the source columns without losing other shadow data that hasn't changed.

If it helps, a SQL Server equivalent might look like the following (which doesn't cater for multiple rows I realise):

CREATE TRIGGER [sample_trigger] ON SampleTable

    UPDATE SampleTable SET
                 ColumnA_VAR = t.ColumnA
                 ,ColumnB_VAR = t.ColumnB
                 ,ColumnC_CLOB = t.ColumnC
    FROM SampleTable t
      INNER JOIN INSERTED i ON t.RecordId = i.RecordId

Can anyone provide a working example for Oracle?
Phil CatterallAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

slightwv (䄆 Netminder) Commented:
You might lost data going from an NVarchar2 to a Varchar2.

Here is your test case:

rop table tab1 purge;
create table tab1(
	columnA nvarchar2(100),
	columnB nvarchar2(300),
	columnC nclob,
	columnA_Var varchar2(100),
	columnB_Var varchar2(300),
	columnC_Var clob

create or replace trigger tab1_trig
before insert or update on tab1
for each row
	:NEW.columnA_Var := :NEW.columnA;
	:NEW.columnB_Var := :NEW.columnB;
	:NEW.columnC_Var := :NEW.columnC;

show errors

insert into tab1(columnA, columnB, columnC) values('a','b','c');
select * from tab1;

update tab1 set columnA='z',  columnB='y',  columnC='x';
select * from tab1;

Open in new window

slightwv (䄆 Netminder) Commented:
Have you thought about a mirrored table instead of additional columns?

What type of Text queries are you going to be doing?

You might be able to get away with a USER_DATA_STORE if you will be searching across columns.

For example if columnA has 'Fred' and columnC has 'Flintstone', if you search for 'Fred Flintstone', will you want the row back?
Phil CatterallAuthor Commented:
Thanks for your responses.

A mirrored table is up for consideration if we cannot achieve this with a simple trigger, it just feels as if this is such a straight forward thing it shouldn't require it.

I'll try creating a dummy table with trigger as per the suggestion but this looks very similar (if not identical) syntax to what we've tried.

Regarding the Text queries, yes multi-column searches are the goal. I've created indexes with  MULTI_COLUMN_DATASTORE and that part seems to be fine, it's just getting the shadow data to update that's going wrong (at the moment). Perhaps having the index set to sync on commit is interfering with the trigger(?) I'll try and decouple things to see what happens.
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

slightwv (䄆 Netminder) Commented:
>>Perhaps having the index set to sync on commit is interfering with the trigger(?)

Not likely since the trigger fires before the commit.

I might consider a USER_DATASTORE:

With this you create a stored procedure that outputs a CLOB that is indexed.

Within this procedure you can convert your NVARCHAR2 and NCLOB data however you want.  Then append the converted data to a CLOB and you are done.

With this you shouldn't need a trigger at all.
slightwv (䄆 Netminder) Commented:
I have a user_datastore sample somewhere.  I'll see if I can find it and mock something up for you.
slightwv (䄆 Netminder) Commented:
See if this would simplify your life somewhat (I'm still thinking you might lose data when converting from NVARCHAR2 to VARCHAR2 but I'll let you worry about this since I don't do anything with multi-byte charactersets).

drop table tab1 purge;
create table tab1(columnA nvarchar2(20), columnB nvarchar2(20), columnC nclob, idx_col char(1));

create or replace procedure myproc(rid in rowid, tlob in out clob) is 
	for c1 in (select columnA, columnB, columnC from tab1 where rowid = rid) loop
		dbms_lob.writeappend(tlob,length(c1.columnA || ' '),c1.columnA || ' ');
		dbms_lob.writeappend(tlob,length(c1.columnB || ' '),c1.columnB || ' ');
	end loop; 

show errors

	ctx_ddl.create_preference('MY_DATASTORE', 'user_datastore'); 
	ctx_ddl.set_attribute('MY_DATASTORE', 'procedure', 'myproc'); 
	ctx_ddl.set_attribute('MY_DATASTORE', 'output_type', 'CLOB'); 

create index tab1_idx on tab1(idx_col) indextype is ctxsys.context parameters('Datastore MY_DATASTORE sync(on commit)');

insert into tab1 values('Fred','Flintstone','Lives in Bedrock',null);
insert into tab1 values('Barney','Rubble','Lives next door to Fred',null);

select * from tab1 where contains(idx_col,'Fred',1) > 0;
select * from tab1 where contains(idx_col,'Bedrock',1) > 0;

--now the 'magic'
select * from tab1 where contains(idx_col,'near((Fred,Flintstone),10,true)',1) > 0;

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Phil CatterallAuthor Commented:
This is looking very promising, thanks for the comprehensive example.

Excuse my ignorance, but does the alternative approach prohibit us from making relevancy-based searches using the about() operator?

If I search with:

... WHERE CONTAINS(search_idx_placeholder, :Terms, 1) > 0

Passing the :Terms param the value 'about(some phrase)' I get zero results. Using the old index the same query returned data.

Note that the query is fixed (but can be modified). The goal is to allow a user to search by phrase and about() was giving us that.

I can use CONTAINS() on a single word (ie without wrapping with about()) and the expected record is returned so I'm reasonably sure the data is there.

Any ideas?
Phil CatterallAuthor Commented:
Sorry I've noticed that some phrases are working with about(), could be a freak example the keyword I was searching happened to be "void".

I'll keep testing, I think we might be there now thank you.
Phil CatterallAuthor Commented:
So close.

The index doesn't appear to refresh when we update data in the included fields. Is there something else we need to do to keep the index in sync?

Note we cannot modify the UPDATE statements in any way since they are auto-generated by a closed system.
slightwv (䄆 Netminder) Commented:
I'll play around with the user_datastore and updates.  I've only played with them a little which is why I had that laying around.

>>could be a freak example the keyword I was searching happened to be "void".

About queries rely on a compiled knowledgebase.  Oracle provides one but I'm not sure how detailed it is.  You might also need to compile it (can't remember if this is done by default).

About queries get even more fun when you start adding your own terms to the Thesaurus.

Check out this command:
ctxkbtc -user ctxsys/ctxsys -name DEFAULT
slightwv (䄆 Netminder) Commented:
Just like the multi-column datastore (at least as I remember it), you need to update the column the index is based on for the index to resync.

In the example above that I posted, if you update one of the columns, you need to also update idx_col (just set it to null) and the index will resync.

update tab1 set columnA='Wilma', idx_col=null where columnA='Fred';

You might look into a trigger to make sure idx_col is touched on any DML.
Phil CatterallAuthor Commented:
I couldn't change the update statements, however adding a simple trigger to update the placeholder column was enough to kick-start the index sync.

Thanks for all your help with this.
Phil CatterallAuthor Commented:
Provided a better solution than the one we were aiming for by removing the need for unnecessary table columns.
slightwv (䄆 Netminder) Commented:
>>Thanks for all your help with this.

Glad to help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Oracle Database

From novice to tech pro — start learning today.