Create trigger for several rows

Question

I have table users AND orders. After every UPDATE row in orders. I want update DATA in users table namely concat(OLD.DATA + ID which was updated).

Table 'users'.

ID  NAME    DATA
1   John    1|2
2   Michael 3|4
3   Someone 5

Table 'orders'.

ID  USER    CONTENT
1   1       ---
2   1       ---
3   2       ---
4   2       ---
5   3       ---

For example:

SELECT `data` from `users` where `id` = 2; // Result: 3|4
UPDATE `orders` SET '...' WHERE `id` > 0;
**NEXT LOOP**
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 1;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 1;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 2;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 2;
UPDATE `users` SET `data` = concat(OLD.data, ID.rowUpdated) WHERE `user` = 3;

Result:

SELECT data from users where id = 1; // Result: 1|2|1|2
SELECT data from users where id = 2; // Result: 3|4|3|4
SELECT data from users where id = 3; // Result: 5|5

How can I do it?

Possible duplicate of [Is storing a delimited list in a database column really that bad?](https://stackoverflow.com/questions/3653462/is-storing-a-delimited-list-in-a-database-column-really-that-bad) .. ideally you should have a `user_order` table where you store the relations between a user and a order. — Raymond Nijland, Mar 08 '19 at 18:29
The question you linked is clearly relevant in this situation, but I don't see how his question is even remotely similar to the alleged "duplicate". — Lord Elrond, Mar 08 '19 at 20:39
I have removed the Oracle tag as you appear to be using MySQL and not Oracle. Please make sure to properly tag your questions. — Mark Rotteveel, Mar 09 '19 at 10:59

score 0 · Accepted Answer · answered Mar 08 '19 at 21:31

I think you are making the same mistake I made not too long ago, ie storing an array/object in a column.

I would recommend using the following tables in your scenario:

users

+-----------+-----------+
|    id     | user_name |
+-----------+-----------+
|     1     |   John    |
+-----------+-----------+
|     2     |  Michael  | 
+-----------+-----------+

orders

+-----------+-----------+------------+
|    id     | user_id   |date_ordered|   
+-----------+-----------+------------+
|     1     |     1     | 2019-03-05 |
+-----------+-----------+------------+
|     2     |     2     | 2019-03-05 |
+-----------+-----------+------------+

Where user_id is the foreign key to users

sales

+-----------+-----------+------------+------------+------------+
|    id     | order_id  |  item_sku  |    qty     |   price    |
+-----------+-----------+------------+------------+------------+
|     1     |     1     |    1001    |     1      |   2.50     |
+-----------+-----------+------------+------------+------------+
|     2     |     1     |    1002    |     2      |   3.00     |
+-----------+-----------+------------+------------+------------+
|     3     |     2     |    1001    |     2      |   2.00     |
+-----------+-----------+------------+------------+------------+

where order_id is the foreign key to orders

Now for the confusing part. You will need to use a series of JOINs to access the relevant data for each user.

SELECT 
t3.id AS user_id,
t3.user_name,
t1.id AS order_id,
t1.date_ordered,
SUM((t2.price * t2.qty)) AS order_total   
FROM orders t1
JOIN sales t2 ON (t2.order_id = t1.id)
LEFT JOIN users t3 ON (t1.user_id = t3.id)
WHERE user_id=1
GROUP BY order_id;

This will return:

+-----------+--------------+------------+------------+--------------+
|  user_id  |  user_name   |  order_id  |date_ordered|  order_total |   
+-----------+--------------+------------+------------+--------------+
|     1     |    John      |      1     | 2019-03-05 |     8.50     |
+-----------+--------------+------------+------------+--------------+

These type of JOIN statements should come up in basically any project using a relational database (that is, if you are designing your DB correctly). Typically I create a view for each of these complicated queries, which can then be accessed with a simple SELECT * FROM orders_view

For example:

CREATE 
    ALGORITHM = UNDEFINED 
    DEFINER = `root`@`localhost` 
    SQL SECURITY DEFINER
VIEW orders_view AS (

  SELECT 
  t3.id AS user_id,
  t3.user_name,
  t1.id AS order_id,
  t1.date_ordered,
  SUM((t2.price * t2.qty)) AS order_total   
  FROM orders t1
  JOIN sales t2 ON (t2.order_id = t1.id)
  LEFT JOIN users t3 ON (t1.user_id = t3.id)
  GROUP BY order_id
)

This can then be accessed by:

SELECT * FROM orders_view WHERE user_id=1;

Which would return the same results as the query above.

Depending on your needs, you will probably need to add a few more tables (addresses, products etc.) and several more rows to each of these tables. Very often you will find that you need to JOIN 5+ tables into a view, and sometimes you might need to JOIN the same table twice.

I hope this helps despite it not exactly answering your question!

score 0 · Answer 2 · answered Mar 09 '19 at 09:04

It is probably a bad idea to update the USERS table after inserting into (or updating) the ORDERS table. Avoid storing data twice. In your case: you can always get all "order ids" for a user by querying the ORDERS table. Thus, you don't need to store them in the USERS table (again). Example (tested with MySQL 8.0, see dbfiddle):

Tables and data

create table users( id integer primary key, name varchar(30) ) ;

insert into users( id, name ) values
(1, 'John'),(2, 'Michael'),(3, 'Someone') ;

create table orders( 
  id integer primary key
, userid integer
, content varchar(3) references users (id) 
);

insert into orders ( id, userid, content ) values
 (101, 1, '---'),(102, 1, '---')
,(103, 2, '---'),(104, 2, '---'),(105, 3, '---') ;

Maybe a VIEW - similar to the one below - will do the trick. (Advantage: you don't need additional columns or tables.)

-- View
-- Inner SELECT: group order ids per user (table ORDERS).
-- Outer SELECT: fetch the user name (table USERS)
create or replace view userorders ( 
  userid, username, userdata 
)
as
select
  U.id, U.name, O.orders_
from ( 
  select 
    userid
  , group_concat( id order by id separator '|' )  as orders_
  from orders
  group by userid
) O join users U on O.userid = U.id ;

Once the view is in place, you can just SELECT from it, and you will always get the current "userdata" eg

select * from userorders ;

-- result 
userid  username    userdata
1       John        101|102
2       Michael     103|104
3       Someone     105


-- add some more orders
insert into orders ( id, userid, content ) values 
 (1000, 1, '***'),(4000, 1, '***'),(7000, 1, '***')
,(2000, 2, ':::'),(5000, 2, ':::'),(8000, 2, ':::')
,(3000, 3, '@@@'),(6000, 3, '@@@'),(9000, 3, '@@@') ;


select * from userorders ;

-- result
userid  username  userdata
1       John      101|102|1000|4000|7000
2       Michael   103|104|2000|5000|8000
3       Someone   105|3000|6000|9000

Create trigger for several rows

2 Answers2

users

orders

sales