Robert W
|
| Posted: 09/30/2002, 3:28 AM |
|
Sorry, I misstyped so I submitted an empty message...
I am evaluating CCS at the moment, and I'm quite new to this world. Hope anyone can help me.
My question is if it is possible to have a record to automatically update the current table AND another or more tables.
For instance, we have a table where the user adds a machine (type, model etc, and it's primary ID (machine_nr)). When we submit that data, we would like the machine_nr (for instance number 3), pass to the table machine_check's Primary ID, machine_check_id.
Grateful for any help!
|
|
|
|
aarl
|
| Posted: 09/30/2002, 4:12 AM |
|
...try a trigger in the database table...
create trigger
on insert,update
|
|
|
|
Robert W.
|
| Posted: 09/30/2002, 5:48 AM |
|
Thank you for the tips. But I still have no luck... I tried Properties/Data/Custom Insert and Update, but then it only updates the other table i choose, not both. Maybe it's possible via an Event, but I don't know how to do it. Someone who have detailed tips?
Thank you in advance,
Robert W.
|
|
|
|
Lorenz
|
| Posted: 09/30/2002, 6:41 AM |
|
Hi!
I use CC2, not CCS, but I hope it works same way.
Try to put in "After update" event something like this:
$sSQL = "UPDATE .... SET ....." ;
$db->query($sSQL) ;
$sSQL1 = "UPDATE ..... SET ....." ;
$db->query($sSQL1) ;
You have to fill this queries with your own elements (ad example using functions like "get_db_value"). In this event you have all values charged in fields, so it shouldn't be difficult.
If it doesn't works you have to use odbc functions.
Tell me!
|
|
|
|
Robert W.
|
| Posted: 10/01/2002, 3:26 AM |
|
A little uppdate on my efforts...
I have tried various versions of adding a Custom Code (MySQL) in Events/After Update. I still have no luck. I tried many different approaches with INSERT INTO and UPDATE. I have also tried to make "maskin_nr" as an variable and stored it, and then tried to INSERT or UPDATE it in the other table. But it refuses to send "maskin_nr" to the other table. It records correctly in it's own table, though.
I appreciate the tips I have gotten so far. I will try some more, but I'm running out of ideas. My hope was to have a couple of separate tables, all with the primary ID "maskin_nr", so if we add a machine in table "machine", it also adds "machine_nr" in, for instance, table "spare_parts", "extra_equipment", "yearly check", "service" and so on... So we can make a logic structure with easier updates on the tables.
If anyone have more tips, I would be most grateful. I want to thank those who have tried to help me so far.
Robert W.
|
|
|
|
BB
|
| Posted: 10/01/2002, 1:32 PM |
|
Please see the sample I put in the CCS Sniplets site http://kabwebs.com/ccsniplets/index.php
|
|
|
|
Robert W.
|
| Posted: 10/02/2002, 2:29 AM |
|
Thank's BB for the excellent link! Many useful snippets there!
However, I had no luck with the example you gave me. I guess I must do something wrong. It always give me an error message on the line:
" . $DBMIS->ToSQL($last_id,ccsInteger) . ", " "
Any suggestions?
Thank's in advance,
Robert W.
|
|
|
|
aarl
|
| Posted: 10/02/2002, 5:16 AM |
|
if your using ms sqlserver, the cleanest way to handle this is to use a trigger on the table itself...
update the 'machine_check' table when a new record is inserted by using the @@identity value of the inserted record and the value of the machine_nr. see documentation on creating triggers in ms sql...pass as a parameter to a sp that inserts new record.
An easier way to handle this is to add a user interface for adding new machine_checks, and enforce integrity between the two tables....
|
|
|
|
Robert W.
|
| Posted: 10/02/2002, 5:29 AM |
|
Hi aarl,
Thank your for the response! However I'm using MySQL (version 3.23.38), on a Linux system. I'm new to SQL and PHP, and I'm reading everything I can lay my eyes on  It seems that Foreign Keys would be the way to go. With that function you are supposed to be able to autoupdate different tables, if the tables have the same Primary ID, let's say "machine_nr". Maybe MySQL v4.x supports it? I hoped it was possible to make an Event in CCS that took care of autoupdating many tables at the same time. I dont want the user to have to write in the same number("machine_nr") in maybe 8 different tables. If they missed one table, it would loose integrity, and the database would be faulty.
Robert W.
|
|
|
|
|
|
