Link to home
Start Free TrialLog in
Avatar of Phil Catterall
Phil Catterall

asked on

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)
ColumnC NCLOB

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

ColumnA_VAR VARCHAR(100)
ColumnB_VAR VARCHAR
ColumnC_CLOB CLOB

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
FOR INSERT, UPDATE
AS
IF UPDATE (ColumnA) OR UPDATE (ColumnB) OR UPDATE(ColumnC)
BEGIN

    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
END

Can anyone provide a working example for Oracle?
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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
begin
	:NEW.columnA_Var := :NEW.columnA;
	:NEW.columnB_Var := :NEW.columnB;
	:NEW.columnC_Var := :NEW.columnC;
end;
/

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

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?
Avatar of Phil Catterall

ASKER

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.
>>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:
http://docs.oracle.com/cd/E11882_01/text.112/e24436/cdatadic.htm#i1006810

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.
I have a user_datastore sample somewhere.  I'll see if I can find it and mock something up for you.
ASKER CERTIFIED SOLUTION
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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?
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.
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.
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
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';
commit;

You might look into a trigger to make sure idx_col is touched on any DML.
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.
Provided a better solution than the one we were aiming for by removing the need for unnecessary table columns.
>>Thanks for all your help with this.

Glad to help!