Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 ?

share|improve this question

1 Answer 1

up vote 0 down vote accepted

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

share|improve this answer
    
Yessssss................It's working.... Thanks a ton..!! –  user23750 May 25 '13 at 6:46

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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