1

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 ?

1 Answer 1

0

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 !!!

1
  • Yessssss................It's working.... Thanks a ton..!!
    – user23750
    May 25, 2013 at 6:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.