| Author |
Topic |
|
scholin
Yak Posting Veteran
56 Posts |
 Posted - 2009-08-08 : 23:32:57
|
| I am new to SQL but not databases. I am using Alpha Five as my Web front-end software which is great but has some limitations with relationships and SQL for the web. I need a one-to-one relationship with two tables where if the field "SSEP" in my dbo.Patient table is True, then it will automatically check my dbo.ssep table to see if the "Patient_ID" exist (file created in dbo.ssep). If so, do nothing but if not, create a record and copy "Patient_ID" over to dbo.ssep.How do I accomplish this? Would this be a trigger? Can you provide the query as an example that I could actually use? Thank you for your time - I appreciate it! |
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 05:46:51
|
I would always say: If there is a one-to-one relationship then put it together in one table.
No, you're never too old to Yak'n'Roll if you're too young to die. |
 |
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 07:40:26
|
| Normally I would agree. This is a case where one patient can have a variety of test procedures done but never more than one test. I don't want a table with 300 fields of all the possible tests when most of time only 50 fields at most would be needed between the two tables. Thus a one to one would make it the patient table smaller and more manageable. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 07:51:16
|
Without knowing the table structure it is hard to show you the code for "create a record and copy "Patient_ID" over to dbo.ssep".What means: if the "Patient_ID" exist (file created in dbo.ssep).
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 08:31:31
|
| For this situation, I have the dbo.Patient table and dbo.SSEP table. dbo. The main table is dbo.Patient with a primary key "Patient_ID".The dbo.SSEP table has a field call "patient_ID" as well, which is the linking field.In the dbo.patient table there is a logical field (check box) called "SSEP". If this is checked, I would like the system to automatically check to see if there is a corresponding record with the same "Patient_ID" in the dbo.SSEP table. If not, then create a new record with just the "Patient_ID". If "Patient_ID exist in the dbo.SSEP table, do nothing. (Even if someone were to uncheck the SSEP check box later, I don't care if this dbo.SSEP table still have this data - I only want to make sure that a matching record is crated if one does not exist WHEN the SSEP box is checked).(Normally I would use the Alpha Five Web software to create the new SQL records in the database for any child relationship as needed but I can't set that software to display a single child record without the display wanting to show a new blank record below it unless a child record already exist and I set it to NOT allow Inserts. That is, I can set it to Update an existing record but I don't want to let it Insert too. Can't just Insert one record . . more than you probably want to know! LOL) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 08:40:34
|
Then I would use a trigger with this statement:insert dbo.SSEP(Patient_ID)select DISTINCT Patient_ID from inserted iwhere i.SSEP = 1 -- or whatever is to compare for "checked"...and not exists (select * from dbo.SSEP where Patient_ID = i.Patient_ID)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 09:45:51
|
| Webfred,Once again your help was right on. I had provided you the wrong name for the SSEP table, which was SSEP_Result and the following is what I used - it all seems to work perfectly! Thank you again - I truly appreciate you help.ALTER TRIGGER [dbo].[Create_New_Ssep_Rec] ON [dbo].[Patient]After INSERT, UPDATEASinsert dbo.SSEP_Result(Patient_ID)select DISTINCT Patient_ID from inserted iwhere i.SSEP = 1 and not exists (select * from dbo.SSEP_Result where Patient_ID = i.Patient_ID) |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 09:51:56
|
Fine, you're welcome 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 10:34:06
|
| I spoke too soon. From the SQL side, if I enter a new record directly with or without the SSEP_Result check box "True", no problem. However if I enter a new record from the web site I now get an error saying "The database could not find the data you requested". If I remove from the trigger the Insert and just make it Update only, no problem for the SQL side or the Web site. The problem seems to be with creating a new patient record with the trigger from the Web site if I have it set to Insert and Update. Do you see another way to handle the Insert part of the trigger to prevent this? (I realize this is an Alpha Five problem - just don't know if you had another idea or approach that may work)Thanks! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 10:57:33
|
Sorry but I havn't a clue why Alpha Five has a problem when the database itself is inserting a record to a table within a trigger.
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 10:58:26
|
| As I think about this, it appears that the Patient record needs to be created first then INSERT the new SSEP_Result record. I am guessing that Alpha Five is sending an Insert Record transaction and the Trigger with its essentially simultaneous Insert into the SSEP_Result record is causing the conflict. Is there way on Insert of a new Patient record to allow it to first create the Patient record and then create the SSEP_Result record if SSEP is checked or do I do that with two triggers? Thanks! |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 11:11:22
|
| Webfred,How about this - how would you suggest that I could have the SQL just create a new record in SSEP_Result every time a new Patient record is created, and not worrying about checking for the SSEP field. Would that still be a trigger and if so how would you code that? Becuase I notice now if I uncheck the SSEP box in Patient that too now causes an error.Sorry to be a pain here but this will really be a huge help to get me over this hurdle.Thank you again. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 13:53:17
|
I really would like to help but I don't know how.If the trigger works when inserting data using a query but it is not working when inserting data via Alpha Five then the problem is Alpha Five and not the trigger...Sorry but I don't have a solution for that.Maybe here is a place to help you:http://msgboard.alphasoftware.com/alphaforum/
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 14:25:14
|
| I would like to try it where the SQL automatically creates a new record in the dbo.SSEP-Result table for all new Patient records. I have been playing around but no success in the sql code.So if you were going to have the sql automatically create a new record in the dbo.SSEP_Results table when a dbo.Patient table was created, how would you do that?Would you use a trigger or a procedure or what? I would like to try this out; one for learning purposes and two I think it would work if was Alpha was not checking against a trigger on a field.BTY, thank you for the Alpha forum info, I am very familiar with that. In this case I am working on Alpha's new beta AJAX version and I am re writing my current Alpha website to have a SQL backend instead of Alpha's dbf database, which I have used for years. So the truth is I have a lot of issues going against me but I am making some headway and enjoy learning SQL. I have to say that Alpha new AJAX version 10 is going to be a boom for many people!Let me know what you think if you can. Thanks. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 14:45:07
|
quote:
So if you were going to have the sql automatically create a new record in the dbo.SSEP_Results table when a dbo.Patient table was created, how would you do that?
That's the point!The trigger we've created is what I would do!With checking the checkbox-valueALTER TRIGGER [dbo].[Create_New_Ssep_Rec] ON [dbo].[Patient]After INSERT, UPDATEASinsert dbo.SSEP_Result(Patient_ID)select DISTINCT Patient_ID from inserted iwhere i.SSEP = 1 and not exists (select * from dbo.SSEP_Result where Patient_ID = i.Patient_ID) Without checking the checkbox-valueALTER TRIGGER [dbo].[Create_New_Ssep_Rec] ON [dbo].[Patient]After INSERT, UPDATEASinsert dbo.SSEP_Result(Patient_ID)select DISTINCT Patient_ID from inserted iwhere not exists (select * from dbo.SSEP_Result where Patient_ID = i.Patient_ID)
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 15:06:52
|
| Webfred,First I want to thank you for sticking with me on this. Your code and your help was perfect and much appreciated. Thanks to you I did indeed learn some more about SQL (and Alpha Five).There is something with Alpha Five and triggers that is screwy here that I need to investigate with the company. Maybe it is the beta but I will find out. I have a large medical data portal that is working fine but I want to expand so a re-write is in order. What a drag! But for the most part I like how it is shaping up. I just have to get over some hurdles and it will all be worth it.Thank you again - have a good evening! |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 15:27:56
|
Good luck - I think you will work it out.Have a nice evening too!Webfred
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
scholin
Yak Posting Veteran
56 Posts |
Posted - 2009-08-09 : 15:33:44
|
| I promise - last message but good news to report.I am using your code "without Checkbox" to create a new record no matter what. I just took out the "Update" and left it as "Insert" only and it all works great - from both Alpha and of course SQL. Problem solved (at least a decent work around). Thanks you so much for all your attention to this. |
|
|
|
webfred
Master Smack Fu Yak Hacker
8781 Posts |
Posted - 2009-08-09 : 15:41:48
|
Pooh! 
No, you're never too old to Yak'n'Roll if you're too young to die. |
|
|
|
|
Upsert or forced Insert question