Dynamic Trigger update to column

[Former Member]

Hello Experts,

I have two tables. Table A with many columns for each Symptom and Table B with one column for Symptom. If the input 'FEVER' is inserted into TableB Symptom column, I would like to set up a trigger which automatically puts a 1 value into Table A's Fever Column. I know I can write an if statement:

If tableB.symptom=FEVER

then insert into tableA.FEVER (-1)

But there can be many symptoms and this if statement would drag on and I'm thinking their is a better way.

How would I go about doing this? Would I use a Stored Procedure?

thanks,

Connor

[lbreddemann · Accepted solution]

Sounds like you want to perform a kind of PIVOT operation here.

What is the purpose (application wise) of this information duplication? Effectively you are going to store the same information multiple times here - what's the added value?

Wouldn't a SELECT on the original table be sufficient instead? Building a static transpose-view is rather simple.

Using triggers is the wrong solution in most cases. It hides data design and transformation logic behind a DML operation.

- Lars

[Former Member · Accepted solution]

Thanks for your response Lars.

The purpose: Well once a person is suspected to have an illness we want to take them out of TableB and move them to TableA. So its not really a duplication, rather a movement of data. I want to move it to this tableA because we have an application tied to it. How would you go about doing this?

thanks

[lbreddemann · Accepted solution]

As I see it it is still data duplication.

Basically you have the information about e.g. FEVER for a specific patient.

Now, your data model can have a table for patients and one for diagnoses.

For the application it is transparent whether the patients attributes (FEVER =1 in this case) are stored directly in the table or if the table is actually a view that combines the PATIENTS and the DIAGNOSES tables.

With the duplication of data like this, you create consistency problems that need to be dealt with. So I would avoid this.

The term "data movement" really doesn't make sense in this context, as it would be used in an information life cycle context. With relational databases and especially with SAP HANA we try to avoid this kind of "data movement" - just look at what the simplification for the ERP suite did.

There we got rid of all these intermediate tables that got updated in dependence of some other table, because the information was already available in the system and just needed to be joined/transformed to fit into a different view.

So, I would go and look into providing the application with the required data but without copying it. A SQL view can work beautifully for this.

- Lars

[Former Member · Accepted solution]

Thanks Lars, I understand what you're saying and will try to make it work with a view rather than duplicating it.

[former_member210482]

Hi Connor,

read the data in symptom field to a variable(var_symptom).. Requires some work around.. by considering timestamp of record inserted or some other relevant mechanism.

then use this var_symptom in your if condition using the concepts of dynamic sql. something like

if tableB.symptom = :var_symptom

then

exec 'insert into tableA.' || :var_symptom .....

Regards,

Safiyu

[former_member185199]

Even Table A is bad designed already because every time there is a new Symptom you havent thougt about at designtime you need to add a new column to A