Mysql to help write a trigger, very urgent-CodePudding

Database using two tables and table structure is as follows:

Requirements:
When new_table insert a row, there was a row if new_table1 can record the date and the item is found in the new_table, will new_table in accord with the date and the number of all the records of the item, synthetic material 1, 2, synthetic materials, synthetic materials 3 addition, total synthetic material is a synthetic material 1, 2 synthetic material, synthetic material 3 together, and then put all the synthetic materials 3 addition

If can't find in the new_table1 and new_table ingredients date and items conform to record, will be new_table assigned to the newly inserted the line data new_table1, and calculate the total synthetic materials (still is synthetic material 1, synthetic material 2, composite additive)

The total mean every moment, new_table1 same items on the same day there can be at most one record

I ask you, thank you

CodePudding user response:

Added, the table data is new_table, no data is new_table1

CodePudding user response:

When new_table insert a row
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- that trigger on new_table
Will new_table in accord with the date and the number of all the records of the item, synthetic material 1, 2 synthetic material, synthetic material 3 add
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- you want to update is still new_table
MySQL does not support such behavior

CodePudding user response:

refer to the second floor ZJCXC response:

when new_table insert a row
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- that trigger on new_table
Will new_table in accord with the date and the number of all the records of the item, synthetic material 1, 2 synthetic material, synthetic material 3 add
-- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- -- you want to update is still new_table
MySQL does not support such behavior

I might say not clear, here update combined refers to update new_table1

CodePudding user response:

, establish a unique index on date and item 
The alter table new_table1 add unique (date, goods); 

- built on new_table trigger 
Create the trigger tr_insert_new_table after insert on new_table for each row 
Insert into new_table1 (date, items, 1 synthetic material, synthetic material 2, synthetic material 3) 
1 values (date, items, synthetic material, synthetic material 2, synthetic material 3 
On the duplicate key update 
Synthetic material 1=values synthetic materials (synthetic material 1) + 1, 
Synthetic material 2=values (synthetic material 2) + 2 synthetic material, 
Synthetic material 3=values (synthetic material 3) + synthetic material 3 
;

CodePudding user response:

Wrote a new less.

, establish a unique index on date and item 
The alter table new_table1 add unique (date, goods); 

- built on new_table trigger 
Create the trigger tr_insert_new_table after insert on new_table for each row 
Insert into new_table1 (date, items, 1 synthetic material, synthetic material 2, synthetic material 3) 
Values (new. Date, new items, new synthetic material 1, new, synthetic materials, 2. The new synthetic material 3) 
On the duplicate key update 
Synthetic material 1=values synthetic materials (synthetic material 1) + 1, 
Synthetic material 2=values (synthetic material 2) + 2 synthetic material, 
Synthetic material 3=values (synthetic material 3) + synthetic material 3 
;

CodePudding user response:

In this operation, cowhide,