How to assign a foreign key value using trigger a before inserting

Question

I have a script like this:

There are two tables, table1 and table2 . table1 has a primary key pkey and table2 now has a foreign key fkey during insertion, if a foreign key is provided, the value should be inserted as is. Otherwise, it must obtain the primary key from table1 using some calculations and determine the foreign key to be inserted. How to do it?

I am using MySql 5.0

EDIT

In my script, table1 stores billing information, that is, table1 has invoices and the total amount payable. The client pays a certain amount of the total outstanding balance or will pay for a specific account. I want to do here. When I have not been given bill_id (which is the primary key in table1 and the foreign key in table2 ), I would like to find the oldest account that should be in table1 , and subtract the amount due and further subtract the remaining amount, if any, from the next invoice in the order. I want to do this in a database layer, not at the top level. Therefore, when the insertion is performed without a value for the foreign key, the value must be retrieved and placed by a trigger or otherwise directly inserted. How do I achieve this?

Using the answers given here, I tried this:

 CREATE DEFINER=`root`@`localhost` TRIGGER `inflow_pay_done_insert` BEFORE INSERT ON `inflow_pay_done` FOR EACH ROW BEGIN DECLARE pkey INT; SET pkey = (SELECT bill_id from inflow_bills where payment_stat = 0 and rs_id = NEW.rs_id order by time_stamp limit 1); SET NEW.bill_id = IF(NEW.bill_id , NEW.bill_id , pkey); UPDATE raw_mat_sup rms SET rms.outstanding_bal_payable = rms.outstanding_bal_payable - NEW.amount where rms.rs_id = NEW.rs_id; END| 

and I get the following error when I try to insert into inflow_pay_done:

/ * SQL error (1048): Column "bill_id" cannot be null * /

Answer 1

you can use the subquery in the BEFORE INSERT trigger for this.

 DELIMITER | DROP TRIGGER `inflow_pay_done_insert`| CREATE TRIGGER `inflow_pay_done_insert` BEFORE INSERT ON `inflow_pay_done` FOR EACH ROW BEGIN UPDATE raw_mat_sup rms SET rms.outstanding_bal_payable = rms.outstanding_bal_payable - NEW.amount WHERE rms.rs_id = NEW.rs_id; NEW.bill_id = IF(NEW.bill_id, /* if "bill_id" is provided in INSERT statement, use provided value */ NEW.bill_id, /* if not, query other table for the correct value */ ( /* this subquery is just an example, put your own query here*/ SELECT bill_id FROM inflow_bills /* find customers newest bill based on newest date and customer id */ WHERE payment_stat = 0 AND rs_id = NEW.rs_id ORDER BY time_stamp DESC LIMIT 1 ) ); END; | delimiter; 

UPDATE

Due to MySQL Bug , this will only work when the column is allowed to NULL and there is no restriction on the column (-> foreign key). The reason is that MySQL, unlike other DBMSs, checks for constraints before and starts the BEFORE INSERT trigger and effectively avoids the execution of the trigger, which corrects the entered data.

The only solution for this, until the behavior of MySQL changes, is to use STORED PROCEDURE instead of a simple INSERT . Then the stored procedure is called with the values ​​to be inserted. The procedure performs data correction (as in this case: choosing the correct bill_id ), and then INSERT is performed from the stored procedure.

UPDATE II

This error appears to be fixed in 5.7.1. List of changes:

If a column is declared NOT NULL, it is not allowed to insert NULL into the column or update it to NULL. However, this restriction was applied even if there was a BEFORE INHERITANCE (or BEFORE UPDATING trigger) that set the column to a value other than NULL. Now the constraint is checked at the end of the statement in accordance with the SQL standard.