Skip to Content
avatar image
Former Member

Dynamic Trigger update to column


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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

3 Answers

  • Best Answer
    Jan 20, 2015 at 05:26 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 21, 2015 at 12:19 PM

    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

    Add comment
    10|10000 characters needed characters exceeded

  • Jan 21, 2015 at 12:24 PM

    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

    Add comment
    10|10000 characters needed characters exceeded