Mysql – Trigger not Working

MySQLtrigger

I created trigger on table PENDING.

Pending table has 3 columns

  • uniqueId
  • duration
  • maxDuration

I have another table COUNT with 2 columns

  • req_id
  • total

Here is my trigger–

CREATE TRIGGER plus3second BEFORE INSERT
ON PENDING
FOR EACH ROW
BEGIN
DECLARE req_id varchar(25);
DECLARE total int(11);
DECLARE duration int(2);

SET req_id = SUBSTR(new.uniqueId, 1, 14);

Select total into total from COUNT where req_id = 'req_id';

IF total > 100 THEN

 SET duration = new.duration + 3;

  IF duration < new.maxDuration Then
     SET new.duration = duration;
  END IF;


END IF;


END

Trigger created successfully.
I fired these queries on COUNT and PENDING-

insert into COUNT values ('77711422099653',200);
insert into PENDING (uniqueId, duration, maxDuration) values ('77711422099653919893277163', 3, 20);

But trigger not working …Where is the problem ?

Best Answer

I made a few corrections

  1. Changed local variable total to given_total
  2. Changed local variable req_id to given_req_id
  3. Changed local variable duration to given_duration
  4. Placed Backquotes around COUNT because COUNT is a reserved word (just a precaution)

With these corrections, here is the new trigger:

CREATE TRIGGER plus3second BEFORE INSERT
ON PENDING
FOR EACH ROW
BEGIN
DECLARE given_req_id varchar(25);
DECLARE given_total int(11);
DECLARE given_duration int(2);

SET given_req_id = SUBSTR(new.uniqueId, 1, 14);

Select total into given_total from `COUNT` where req_id = given_req_id;

IF given_total > 100 THEN

 SET given_duration = new.duration + 3;

  IF given_duration < new.maxDuration Then
     SET new.duration = given_duration;
  END IF;


END IF;


END

What was wrong before? In your original trigger, you had Select total into total from COUNT where req_id = 'req_id';. That was looking for the req_id actually being equal to the string 'req_id'. That's why I made the other corrections, especially correction #2.

Give it a Try !!!

Related Question