Update various tables depending on survey answer

Question

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?

Answer 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.