2

I've created a survey solution and it work just fine. All questions have multiple options to choose from as answer. What I would like to achieve is when a certain option is saved as an answer the value or values of a another table is to be updated. The table or tables that are to be updated may vary depending on the answer of the user and the data type of column might also vary.

I was thinking about using a metadata table, in this example AffectedData, containing information about the tables, the values and data types. Something like this pseudo code:

CREATE TABLE AffectedData
    ID int
    TableName nvarchar(50) NOT NULL -- table to update
    ColumnName nvarchar(50) NOT NULL -- the col to update
    DataType nvarchar(50) NOT NULL -- the col data type
    DataValue nvarchar(50) NOT NULL -- the value to update with
    Operation int --insert or update

When an answer is inserted a trigger or a procedure might query this table and by using some conditional logic cast the DataValue to the correct data type, using DataType as hint, and insert or update into to the table specified in TableName. In other words I assume I have to use dynamic SQL, using sp_executesql to execute the statement.

Is this a way to go or is there some other wise way of achieving this?

1 Answer 1

1

I would say this is a pretty reasonable solution. You might consider adding an "active" flag or something similar so you can deactivate certain rows from your AffectedData table without deleting them. Also you will need to make sure that any solution you have takes into account the possibility of multiple entries in the metadata table. Either by putting a primary key on it that restricts them or by using a loop or batch solution when you are creating your dynamic SQL.

2
  • Thanks for your input. Yes, each option might change multiple tables and using a composite key seems like the right thing to use. An active flag is a good suggestion.
    – JohnP
    Jan 25, 2014 at 17:23
  • For updates some kind of predicate has to specified as well to be used in a where clause. In this case one has to specify the column, datatype and value, for example UpdateWhereColumn, UpdateWhereDataType and UpdateWhereValue
    – JohnP
    Jan 27, 2014 at 9:02

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.