Please some one help me with the below scenario, Say, I have one table named "CTGRY", having columns col1,col2,col3,col4. Another table named "CODE", having columns cd,desc. If there is an update or insert to the CTGRY table, i need to validate the col3 data with the cd column in CODE table. If data is available in the CODE table, then corresponding desc column should be updated or inserted into the col4 in CTRGY table.
From your question description, it looks like you cannot have a formal relationship between the **col3** column of **CTGRY** and **cd** column of **CODE** because it is possible that the value fed into insert or update statement may or may not be one of the valid **cd** values of the **CODE** catalog table. If the provided value of **col3** matches one of the existing **cd** values then you want to update the **col4** of **CTGRY** table with respective **desc** value from the catalog. This is a pretty brutal violation of normalization because there is no need to store the **desc** column value of **CODE** table in the **col4** column of another table. In other words, **col4** of **CTGRY** is never needed and if you need to produce a select statement displaying the valid descriptions based on the existing **col3** values on the **CTGRY** table then all you really need is a select like this: select a.col1, a.col2, a.col3, b.desc as col4 from CTGRY a left join [CODE] b on a.col3 =
b.cd; The above will show col4 values for the records which have a match from desc column of the CODE table and will display NULL for those records which do not have valid col3 values. If it is possible that the desc values are preferred but the col4 column already has some arbitrary values in the non-matching records then you can try to display the desc as col4 first and if there is no match then opt to display what is already there, i.e select a.col1, a.col2, a.col3, isnull(b.desc, a.col4) as col4 from CTGRY a left join [CODE] b on a.col3 =
b.cd; Additionally, what are you going to do if the previously matching col3 value is now being updated to another value which is not present in the CODE table? Do you then need to update the col4 to NULL? In other words, this design forces you to not only check whether value matches and act accordingly but also to handle the situation when existing valid value is being changed to the rubbish one. This functionality would be quite cumbersome if attempted to be implemented in the trigger because the update of the record with some values might cause the update of the same record with the value for an additional col4 column causing bad recursion. Also, what measures are taken to prevent the update/insert values from including some arbitrary value for col4 column? Hope this helps, Oleg