Update a table based on another table with trigger

user_17613057 0
Viewed 33

What I´m trying to do is:

I have a table "emprestimo" with the sytax like this:

ID User Publicacao Id
1 1 1
2 2 1
3 2 2

I also have a table "publicacao" that is like this:

ID Emprestimos_total
1 1
2 0
3 0

What I´m trying to do is when I insert an "emprestimo" for id 1, in the table "publicacao" the "Emprestimos_total" would go from "1" to "2".

I was thinking about using a trigger like this:

CREATE TRIGGER update_qtq_emp
  AFTER INSERT ON emprestimo
  FOR EACH ROW
BEGIN
  UPDATE publicacao
     SET Emprestimos_total = Emprestimos_total + 1
   where Id = NEW.Id;
END;

Is there any way to do this in SQL?

sql-update mysql sql
1 Answers

I recommend you create a view such as

CREATE [OR REPLACE] VIEW v_publicacao 
AS
    SELECT id, COUNT(id) AS Emprestimos_total
    FROM emprestimo
    GROUP BY id

and query from this just like querying from a table whenever needed.

There might exist wrong results if trigger is disabled by a reason for the current case, and no need to store a data, within another source, which is already computable.

user_58413060
Related