Is it possible to modify the values of an insertion during a trigger ?

Question

Question

Massimo Sebastiani · Mar 30, 2017

Hello, we have a few hundreds of triggers to port from Oracle to Cachè for a migration project, and many of them have to change (for example, normalize a value, null it, etc) the value which is being inserted.

The documentation says "You cannot set {fieldname*N} in trigger code." , so we're unlucky.

Is there a good workaround for this ?

SqlComputeOnChange doesn't seem the best way, but I'm not totally sure: for example normalization and validation could have a better place somewhere else than a trigger.

Discussion (2)0

Log in or sign up to continue

Kyle Baxter · Mar 30, 2017

Triggers are fired after data normalization and validation. Do you think you can/should use your own datatypes, overriding the Normalize or LogicalToDisplay/DisplayToLogical functions. If that doesn't make sense you could have a field calculated off of the stored data? There are lots of options here, the best one is going to depend strongly on the goals you have for the project.

2 0

score 0 · Answer 1 · 2017-04-02T10:25:34-04:00

Hello and thanx a lot for the reply.

The goal was mainly to port as much code as possible very quickly, because we ha a deadline soon (1 month, 20klocs of PL/SQL code): later it will be reengineered properly during a second phase, but it seems that a 1:1 conversion is not possible. Also, we are accessing data by means of SQL only, so anything that is only supported in the OO interface is not good for us.

We found that even initialization is often done inside their triggers (!), so the only option is splitting the trigger code into :

1 )Initialization

2) Validation

3) Normalization

4) Cascading changes to other tables

It seems to me that 1) is a perfect job for INITIALEXPRESSION attribute of properties.

I have tried using a <propertyname>IsValid() and a <propertyName>Normalize() ClassMethods and they seem to work (through SQL) without creating a custom datatype: I don't know if it's currently a good pattern, since I couldn't find it clearly stated in the documentation. If someone can confirm that it's ok, we'll proceed.

4) Is clearly the right job for the trigger, we'll leave this code in the trigger.