February 7, 2016 at 3:07 am
I have a view that has fields code and description in it. for example:
Code: | Description
MM4.0 | Asthma
I want to know if I can use an insert update trigger to do the following, when I select the code, the description must automatically populate?
so basically when I select the code MM4.0 from the table (where the code and description are values in this view) it looks up the code and populates the description, is this possible with a trigger or do I need a SP function
February 7, 2016 at 3:50 am
I'm afraid your post lacks a lot of the details that we need in order to be able to answer it.
First: you say you want a trigger; later you also mention an "SP function". Triggers, stored procedures (SPs), and functions are three very different things. But instead of asking us to provide you with a way to do something in a specific way, why not just describe what you need done functionally and see what methods we come up with? Perhaps a trigger is the right way, perhaps not.
Second: you write "when I select the code, (...)". What exactly does "select" mean in this context? Returning the row from a SELECT query? Clicking the code column somewhere in your user interface? Somethinng else?
Third: you continue "(...), the description must automatically populate". That we can do. But how do you want it to populate? Should we just replace the description in your view with a random series of characters, or should it be populated with something useful? And in the latter case, where should that come from? SQL Server is smart, but not smart enough to make up data on its own, without using a source, and still expect it to be useful. Also, based on the start of your post the description column already is populated, so what exactly are you trying to achieve?
Fourth: you mention both a table and a view in your question. If you want any chance of receiving help, you will have to give us more information about the view and about all tables used in the question. We need the CREATE VIEW statement, CREATE TABLE statements for all tables involved, INSERT statements with a few carefully selected rows of sample data to illustrate the question, and the expected results. Make sure to post all this in a form that we can copy/paste/run in our playground databases (and do so yourself before posting so that you can fix all errors that may be in your script).
Finally, I have the feeling that your question is really about some UI thing. You select the code from a list or dropdown on the screen and then want the description to be shown on the screen. That's not a question we can help you with. SQL Server is a pure backend process, with no UI interaction at all. If this is the case, then I suggest finding a forum for whatever tools you use at the frondend and ask the question there.
(Of course, if I am wrong I will be more than happy to look furthrer into your issue, provided you give us the information we need in order to help you).
February 7, 2016 at 6:29 am
@hugo, my apologies I am just trying to get to see which method will work best as I am fairly new at SQL:
1. what I need to be done functionally is this:
In the UI, when a user there is a field called code and it has a sub field called description. So in the front end when the user clicks code lm40.0, the subfield description needs to auto generate "asthma", I am basically matching a code with a description. each disease code has a description matching the code.
So if I select via the UI lm40.0 in the code field, the underneath in the subfield the description must auto populate to asthma
2. yes selecting the code in the UI but in the backend in sql there is a table called codes with columns code, description
3.The description value is in the codes table, it is associated with a code. Each code has its own description. I.e. Each disease code has a description.
apologies for my ignorance I just want to know the best route to take here
February 7, 2016 at 7:33 am
You're talking about the application's interface. You'll want to react to the dropdown's event that fires when you change the selection, which will vary depending on what environment you're using. If you're using asp.net, it's the SelectedIndexChanged event.
When this event fires, you'll want to query the value from the database for the code selected and then display it below your dropdown. You can do this with an SQL function, an SQL stored procedure, by caching the table of codes and descriptions on the web server and then selecting the value from there, or a host of other approaches.
February 7, 2016 at 8:46 am
Stix83 (2/7/2016)
@Hugo, my apologies I am just trying to get to see which method will work best as I am fairly new at SQL:1. what I need to be done functionally is this:
In the UI, when a user there is a field called code and it has a sub field called description. So in the front end when the user clicks code lm40.0, the subfield description needs to auto generate "asthma", I am basically matching a code with a description. each disease code has a description matching the code.
So if I select via the UI lm40.0 in the code field, the underneath in the subfield the description must auto populate to asthma
2. yes selecting the code in the UI but in the backend in sql there is a table called codes with columns code, description
3.The description value is in the codes table, it is associated with a code. Each code has its own description. I.e. Each disease code has a description.
apologies for my ignorance I just want to know the best route to take here
So as I expected, you are asking about a UI question. There's only little we can do here to help you with that.
Most UIs nowadays use some kind of event model, so when the user clicks Im40.0 it will fire an event handler. That event handler has to find out what code was clicked, then reach out to the database to get the corresponding description. The best way to do this is through a parametrized query. The query will look as follows:
SELECT Description
FROM YourTable
WHERE Code = @Code;
Assuming that your UI is object-oriented, you will feed this query string into a query object. You then add a parameter object to that query object, that declares the parameter @Code, assigns it the proper data type, and sets it value to Im40.0. Then you execute the query and process the results to find the description used, and populate that in the field on your UI's form.
(The above is the bare basic, most developers and most DBAs will prefer to have a stored procedure in the database and then the client calls that stored procedure instead of executing a freeform query. In either case the client code should also have error handling).
Depending on the programming language and tools used, an alternative would be to have the client application retrieve the full list of codes and descriptions once and cache it (store it locally - either on the application server or on the actual end client). The cached copy can then be used later without any need for round-trips to the database. This can be faster, especially when you are on a slow network or when the database server is under pressure. However, the list must not be too large to fit into memory on the client side. And you have to consider how often the data source changes and how bad it is that the application will continue to use the old data from its cache until the cache gets refreshed, either because the application restarts or because you build a cache refresh mechanism. If you do want to use caching, then the query you have to execute (probably at applicaiton startup or when the relevant form is first loaded) looks like this:
SELECT Code, Description
FROM YourTable;
As you see, there is no WHERE clause and no parameter. Make sure to process all rows and store them in a suitable object for local storage (I believe that in .Net a dictionary object is often used for this). Then use that object to find the description for a code in the event handler that starts when a code is clicked.
Long post and still quite vague. Again, for more specific help with writing client code, you probably should visit a forum for the language / front-end tool you are using.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply