MariaDB trigger loop

Question

i'm creating a database for school. This is the problem: i got a table with attributes A,B and C (where C=(A/(A+B))*100). On the update of attributes A or B i need to update C. I tried different things but the trigger keeps going into loop bacause when i update A or B the trigger updates C so it keeps going. This code doesn't work(sintax error):

Create trigger nametrigger
after update of (A or B) on tablename

But i somehow need to specify that the trigger has to activate on the update of A or B and not C.

Yet another reason to keep your code in the client.

Rick James
– Rick James

2017-02-01 21:58:51 +00:00
Commented Feb 1, 2017 at 21:58 — Rick James
– Rick James, Commented Feb 1, 2017 at 21:58

elenst · Accepted Answer · 2017-02-01 21:27:04Z

1

From what you describe, you don't need a trigger, you need C to be a computed column (a.k.a. virtual column).

MariaDB [test]> CREATE TABLE t (
  a INT, 
  b INT, 
  c INT AS ((a/(a+b))*100) PERSISTENT
);

MariaDB [test]> insert into t (a,b) values (1,1);
MariaDB [test]> insert into t (a,b) values (2,2);

MariaDB [test]> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    1 |   50 |
|    2 |    2 |   50 |
+------+------+------+
2 rows in set (0.01 sec)

MariaDB [test]> update t set b = b*2;

MariaDB [test]> select * from t;
+------+------+------+
| a    | b    | c    |
+------+------+------+
|    1 |    2 |   33 |
|    2 |    4 |   33 |
+------+------+------+
2 rows in set (0.00 sec)

This is just an example, not a solution. You need to read about computed columns to decide how exactly it needs to be configured.

answered Feb 1, 2017 at 21:27

elenst

4,0051 gold badge19 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Davide D. Somma Over a year ago

Thanks for the answer. I can do the same even if A and B are attributes in another table?

elenst Over a year ago

No, you can't, as you could find out from the first paragraph of the "Virtual (Computed) Columns" page by the link provided in the question. But as the answer specifically points out, it addresses the situation as you describe it, and you were explicitly saying that all columns belong to the same table: " i got a table with attributes A,B and C..."

Collectives™ on Stack Overflow

MariaDB trigger loop

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related