on 01-20-2015 4:18 PM
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
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
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
User | Count |
---|---|
101 | |
13 | |
13 | |
11 | |
11 | |
7 | |
6 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.