Updating data with an Oracle trigger
Posted on 2013-02-07
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:
We append the new columns to the same table to add (and initialise with copy of source column data):
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
IF UPDATE (ColumnA) OR UPDATE (ColumnB) OR UPDATE(ColumnC)
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?