trigger before insert with if statement within a table

Question

I have another problem with triggers using MySQL. These two tables namely debt and profile are now connected by a trigger that is solved from my other question here in stackoverflow.

CREATE TRIGGER `bal_load_debt` BEFORE INSERT ON  `debt` 
FOR EACH ROW BEGIN
  UPDATE `profile`
  SET `bal` = `bal` + NEW.`amount`
  WHERE `profile`.`id` = NEW.`profile_id`;
END

DEBT

+--+----------+------+
|ID|PROFILE_ID|AMOUNT|
+--+----------+------+
|1 |1         |500   |
+--+----------+------+

PROFILE

+--+-------+-----+------+
|ID|BALANCE|LIMIT|STATUS|
+--+-------+-----+------+
|1 |500    |650  |OPEN  |
|2 |300    |500  |OPEN  |
+--+-------+-----+------+

My new problem is if the new balance is greater or equal than the limit, the status should be changed to CLOSE. I tried modifying the trigger bal_load_debt by adding

  IF `balance` >= `limit`
      THEN UPDATE `profile`
  SET `status` = 'CLOSE';
  END IF;

but results to an error #1054 Unknown column 'balance' in 'field list'

I also tried creating another trigger of an after update on profile table but I think the syntax used is not that effective :(

The goal is if I add another row at debt like:

+--+----------+------+
|ID|PROFILE_ID|AMOUNT|
+--+----------+------+
|1 |1         |500   |
|2 |1         |150   |
+--+----------+------+

The profile table should look like this:

+--+-------+-----+------+
|ID|BALANCE|LIMIT|STATUS|
+--+-------+-----+------+
|1 |650    |650  |CLOSE |
|2 |300    |500  |OPEN  |
+--+-------+-----+------+

I'll be very glad of your helping hands :)

Anand thakkar · Accepted Answer · 2015-11-27 11:36:30Z

1

you should have BEFORE UPDATE Trigger over the profile table with the Following Query check

IF NEW.balance >= LIMIT THAN
    SET NEW.status = 'CLOSE';
END IF

here i am assuming that LIMIT is the total debt to be paid

edited Nov 27, 2015 at 11:36

answered Nov 27, 2015 at 11:07

Anand thakkar

4993 silver badges8 bronze badges

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

7 Comments

user2713803 Over a year ago

I tried this and have an insert at debt but this error came up: #1054 Unknown column 'limit' in 'field list'

user2713803 Over a year ago

I think there is an 'override' problem... new error: #1422 - Can's update table 'profile' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

Anand thakkar Over a year ago

i think SET NEW.status = 'CLOSE' Should Work here

Anand thakkar Over a year ago

can you write down your complete code here as it seems that you are writing into the wrong trigger this should be part of the BEFORE UPDATE trigger of the Profile table and is there any other trigger which is interacting here ?

user2713803 Over a year ago

Is it possible to have a variable for bal + NEW.amount so that we can append an if statement to the current trigger?

|

Collectives™ on Stack Overflow

trigger before insert with if statement within a table

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related