Hello,I am working on a project which have 1 master table and 3 details table. Problem facing when i am trying to insert data using master/detail form. Lets A is master table. X, Y, Z is detail tables. I found that, data inserted successfully to A (master) & Z (detail) table whereas X & Y table shows error message. At the same time my system shows error message and data insert successful message. I checked master/detail data view mode and found that table A & Z has data but table X & Y has no data. I tried another way and found that, data is inserting to any detail table which has no issue with data. Data is not inserting any detail table which data has issue, even due to mysql trigger data validation. In my case, earlier same master table with single detail table was working fine. I don’t understand how come it is accepting partial data is inserting and partial data is rejecting to insert data. In need the changes that, if any error occurs any of the detail table then abort inserting data to the all tables. Referential integrity , cascade delete and update is on.Requesting experts to share their thoughts to overcome this issue?
You should enable Debug and find the error message in the log file so you can find out what was was wrong.Note also that by default transaction is enabled, if anything went wrong, the transaction will be rollbacked, no data will be inserted to the detail table.
I set below code on detail table for preventing users to input quantity then available quantity. Earlier ( when no. of detail table was 1) if user input value was higher then available stock, system shows error message and rollback data from master & detail both table.
// Row Inserting event
function Row_Inserting($rsold, &$rsnew)
{
// Enter your code here
// To cancel, set return value to false
//before_grey_requisition_details_validate
$find_qty = "SELECT
( grey_store_status.quantity - grey_store_status.reguisition_confirm - grey_store_status.reguisition_hold - grey_store_status.return_qty - grey_store_status.return_hold )
FROM
grey_store_status
WHERE
grey_store_status.client_name = '" . $this->link_received_from->CurrentValue . "'
AND grey_store_status.order_no = '" . $this->grd_order_no->CurrentValue . "'
AND grey_store_status.item_description = '" . $this->item_description->CurrentValue . "'
AND grey_store_status.gsm = '" . $this->gsm->CurrentValue . "';";
$get_qty = ExecuteScalar($find_qty);
if ( $this->quantity->CurrentValue > $get_qty ) {
$this->setFailureMessage(" Your requested quantity is higher then available stock!. Company name " . $rsnew["link_received_from"] ." has " . $rsnew["item_description"] ." has available quantity " . $get_qty . " kg.");
}
return false;
}
now, 3 detail table (gray_requisition_detail, production_specification, finishing_specification) is connected with master table (gray_requisition_master) and if user inputted value is higher then available stock then system shows error message but data also inserted to master & 2 detail table. data is not inserted to the detail table where i set above code. result is rollback is not functioning for master & all detail tables.I enabled debug option and got below information message :
[2023-06-30T10:16:03.393061+00:00] log.DEBUG: "START TRANSACTION" {"params":null,"types":null,"executionMS":0.00022292137145996094} []
[2023-06-30T10:16:03.396090+00:00] log.DEBUG: INSERT INTO gray_requisition_master (`customer_name`, `labdip_no`, `req_date`, `requisition_status`, `executed_by`, `checked_by`, `approved_by`, `req_fabric_delivery`) VALUES(?, ?, ?, ?, ?, ?, ?, ?) {"params":["Apparel Village","3","2023-06-30","Pending",null,"No",null,null],"types":[2,1,2,2,2,2,2,2],"executionMS":0.0026099681854248047} []
[2023-06-30T10:16:03.399563+00:00] log.DEBUG: SELECT *, '' AS `receive_confirm` FROM gray_requisition_master WHERE `gray_requisition_master_id` = 118 AND `customer_name` = 'Apparel Village' {"params":[],"types":[],"executionMS":0.00039196014404296875} []
[2023-06-30T10:16:03.400061+00:00] log.DEBUG: SELECT ( grey_store_status.quantity - grey_store_status.reguisition_confirm - grey_store_status.reguisition_hold - grey_store_status.return_qty - grey_store_status.return_hold ) FROM grey_store_status WHERE grey_store_status.client_name = 'Apparel Village' AND grey_store_status.order_no = '8629' AND grey_store_status.item_description = 'CVC Terry' AND grey_store_status.gsm = '220'; {"params":[],"types":[],"executionMS":0.0003190040588378906} []
[2023-06-30T10:16:03.405016+00:00] log.DEBUG: SELECT *, '' AS `receive_confirm` FROM gray_requisition_master WHERE `gray_requisition_master_id` = 118 AND `customer_name` = 'Apparel Village' AND `labdip_no` = 3 {"params":[],"types":[],"executionMS":0.0003590583801269531} []
[2023-06-30T10:16:03.405513+00:00] log.DEBUG: INSERT INTO production_specification (`grd_batch_no`, `labdip_no`, `mc_dia`, `required_dia`, `machine_no`, `shade_percentage`, `enzyme`, `silicon`, `double_part`, `required_hour`, `link_received_from`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {"params":[118,3,5,9,0,0.7,"No Enzyme","No","No","00:00","Apparel Village"],"types":[1,1,1,1,1,2,2,2,2,2,2],"executionMS":0.0002789497375488281} []
[2023-06-30T10:16:03.405961+00:00] log.DEBUG: SELECT * FROM production_specification WHERE `prod_specific_id` = 13 {"params":[],"types":[],"executionMS":0.00025081634521484375} []
[2023-06-30T10:16:03.409511+00:00] log.DEBUG: SELECT *, '' AS `receive_confirm` FROM gray_requisition_master WHERE `gray_requisition_master_id` = 118 AND `customer_name` = 'Apparel Village' AND `labdip_no` = 3 {"params":[],"types":[],"executionMS":0.0004239082336425781} []
[2023-06-30T10:16:03.410039+00:00] log.DEBUG: INSERT INTO finishing_specification (`grd_batch_no`, `labdip_no`, `mc_dia`, `brush`, `peach`, `all_over_print`, `process_1`, `machine_1`, `process_2`, `machine_2`, `process_3`, `machine_3`, `link_received_from`) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?) {"params":[118,3,5,"Deep Brush","Light Peach","Yes",null,null,null,null,null,null,"Apparel Village"],"types":[1,1,1,2,2,2,2,2,2,2,2,2,2],"executionMS":0.0002639293670654297} []
[2023-06-30T10:16:03.410463+00:00] log.DEBUG: SELECT * FROM finishing_specification WHERE `finish_sprcific_id` = 15 {"params":[],"types":[],"executionMS":0.00022602081298828125} []
[2023-06-30T10:16:03.411748+00:00] log.DEBUG: "COMMIT" {"params":null,"types":null,"executionMS":0.0009341239929199219} []
[2023-06-30T10:16:03.685761+00:00] log.DEBUG: SELECT * FROM employee WHERE (`employee_id` = 4) OR (`employee_id` IN (SELECT `employee_id` FROM employee WHERE `reports_to` = 4)) {"params":[],"types":[],"executionMS":0.0017108917236328125} []
[2023-06-30T10:16:03.688717+00:00] log.DEBUG: SELECT * FROM employee WHERE `reports_to` IN (4, 6) {"params":[],"types":[],"executionMS":0.00039696693420410156} []
[2023-06-30T10:16:03.711567+00:00] log.DEBUG: SELECT COUNT( gray_requisition_details.delivery_confirm ) FROM gray_requisition_details WHERE gray_requisition_details.grd_batch_no = '' AND gray_requisition_details.link_received_from = ''; {"params":[],"types":[],"executionMS":0.00049591064453125} []
[2023-06-30T10:16:03.712037+00:00] log.DEBUG: SELECT COUNT( gray_requisition_details.delivery_confirm ) FROM gray_requisition_details WHERE gray_requisition_details.grd_batch_no ='' AND gray_requisition_details.link_received_from = '' AND gray_requisition_details.delivery_confirm = 'Yes'; {"params":[],"types":[],"executionMS":0.0003151893615722656} []
[2023-06-30T10:16:03.759671+00:00] log.DEBUG: SELECT COUNT(*) FROM (SELECT DISTINCT `link_received_from` AS `lf`, `link_received_from` AS `df`, '' AS `df2`, '' AS `df3`, '' AS `df4` FROM grey_febric_receive_details) COUNT_TABLE {"params":[],"types":[],"executionMS":0.0006659030914306641} []
[2023-06-30T10:16:03.760306+00:00] log.DEBUG: SELECT DISTINCT `link_received_from` AS `lf`, `link_received_from` AS `df`, '' AS `df2`, '' AS `df3`, '' AS `df4` FROM grey_febric_receive_details {"params":[],"types":[],"executionMS":0.0004401206970214844} []
[2023-06-30T10:16:03.766453+00:00] log.DEBUG: SELECT COUNT(*) FROM labdip_master {"params":[],"types":[],"executionMS":0.00036597251892089844} []
[2023-06-30T10:16:03.766871+00:00] log.DEBUG: SELECT `labdip_id` AS `lf`, `labdip_id` AS `df`, `received_from` AS `df2`, `approval_status` AS `df3`, '' AS `df4` FROM labdip_master {"params":[],"types":[],"executionMS":0.00026106834411621094} []
Why this is happening ? any idea?
Your Row_Inserting server event returns false all the time (which means abort inserting).
Then how come data is inserting rest of 2 detail & master table?
Make sure you did not remove return true; line int that Row_Inserting server event.
I tried with this below codes but the result is same:
if ( $this->quantity->CurrentValue > $get_qty ) {
$this->setFailureMessage(" Your requested quantity is higher then available stock!. Company name " . $rsnew["link_received_from"] ." has " . $rsnew["item_description"] ." has available quantity " . $get_qty . " kg.");
return false;
} else {
return true;
}
}
Problem remain same. Data is not rollback from all 3 detail table and master table. I can see data successfully inserted to all other tables whereas error message shows on 1 table.
arbei wrote:
Your Row_Inserting server event returns > false > all the time (which means abort inserting).
Abort inserting is not working with all tables.
Please note that the order of inserted records are started from detail tables, after that ended with master table.
So, you may check in Row_Inserted server event that belongs to the master table; whether there is/are any detail table that is not successfull inserted the record.
If so, then you may just delete the related record in another detail table(s), and also delete the master record.
I intentionally input higher value than available stock and input data to other 2 detail table and master table.System shows 2 seperate message after insert a record. One error message shows that inputed data is higher than available stock and data insertion abort. Another message shows data insert success ful. After that I checked and found that data inserted to master & 2 detail table. Only that table which shows error message has no data.Are there any way which can abort data insertion to all detail table & master table if any table shows error message.Please not that if I configure 1 detail table with a master table then data rollback works properly means no data inserted to master & detail table. When are add multiple detail table data rollback is not working.
You may try to call $this->getConnection()->rollback() when you detect error in your Row_Inserting event and return false.
ebinnasir wrote:
Are there any way which can abort data insertion to all detail table & master table if any table shows error message.
I don’t think so. You need to manually handle the cancelling for all other detail tables by yourself, since by default, the other detail tables are not affected with the rollback in another detail table.