0

I have a table that looks something like this:

Columns:
user_id int(11) PK
module_id   int(11) PK
academy_team_id int(11) PK
academy_id  int(11) PK
sort_number int(11)
is_complete int(11)
score_to_pass   int(11)
is_open int(11)

Now i wish to add a trigger so that when you update this table if the value is_complete is equal to 1 then update the next row's is_open and set it to1

I have attempted with the following trigger sql:

   begin
 if new.is_complete = 1 then
   set next.is_open = 1;
 end if ;
end

Sadly this did not work so im not sure how to do it can anyone push me in the right direction?

According to pala_ Answer

im getting the following error when updating my row:

    ERROR 1442: 1442: Can't update table 'user_has_academy_module' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
SQL Statement:
UPDATE `system`.`user_has_academy_module` SET `is_complete`='1' WHERE `user_id`='1' and`module_id`='11' and`academy_team_id`='49' and`academy_id`='29'
Improve this question
5
  • 1
    How do you know what the "next" row is? Is there a column to ORDER BY? Commented May 1, 2015 at 17:24
  • @MarkLeiber well sort_number is one field you could order by Commented May 1, 2015 at 17:26
  • 1
    In an update trigger, you get old and new prefixes to refer to the previous values and the new values. There is no concept of the 'next' value Commented May 1, 2015 at 17:26
  • @MarcRasmussen how does sort_number work? is it sequential? ie is there always one that is +1 of the previous one? until you get to the last one? Commented May 1, 2015 at 17:27
  • @pala_ for each user_id, module_id and academy_team_id there is a sort_number that works +1 untill end yes Commented May 1, 2015 at 17:28

1 Answer 1

Reset to default
1

Your basic trigger body should be something like this:

begin
  if new.is_complete = 1 and (select id from <table> where user_id = new.user_id and module_id = new.module_id and academy_team_id = new.academy_team_id sort_number = new.sort_number +1 ) then
    update <table> set is_open = 1
      where user_id = new.user_id
        and academy_team_id = new.academy_team_id
        and module_id = new.module_id
        and sort_number = new.sort_number + 1;
  end if
end

It will check to see if there IS another thing to set open (based on same user_id, academy_team_id and module_id, and next sequential sort_number), and if there is, set it open.

MySQL cant update the same table the trigger is set on. It will need to be done with a stored procedure instead.

delimiter //
create procedure completeandopen(IN param INT)
begin
  declare next_id integer;
  declare _user_id integer;
  declare _module_id integer;
  declare _academy_team_id integer;
  declare _sort_number integer;

  select user_id, 
         module_id, 
         academy_team_id, 
         sort_number 
  into   _user_id, 
         _module_id, 
         _academy_team_id, 
         _sort_number 
  from tester 
  where id = param;

  update tester set is_complete = 1 where id = param;
  select id 
  into   next_id 
  from tester 
  where id = param + 1 
    and user_id = _user_id 
    and module_id = _module_id 
    and academy_team_id = _academy_team_id 
    and sort_number = _sort_number + 1;
  if (next_id is not null) then
    update tester set is_open = 1 where id = next_id;
  end if;
end//
delimiter ;

I think this should work - i haven't tested on your table structure, and it does assume a unique primary key on your table. If it doesn't have that - it's easy enough to modify.

To use it, just call completeandopen(id of the row to be completed) (after changing the table name from tester to your table name)

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

8 Comments

Hey pala i tried this and i get a syntax error near end ? :s
I'll need something more specific (ie the full error). -- are you setting your delimiters, and changing <table> to your table?
il post my full sql: in my question
I made it work :) however when running an update on the table i get the following error message: ERROR 1442: 1442: Can't update table 'user_has_academy_module' in stored function/trigger because it is already used by statement which invoked this stored function/trigger. SQL Statement: UPDATE Learningbank.user_has_academy_module SET is_complete='1' WHERE user_id='1' andmodule_id='11' andacademy_team_id='49' andacademy_id='29'
I must be tired. Of course this doesn't work. MySQL doesn't let you do it. If you desire this behaviour you'll need to do it in a stored procedure instead
|

Your Answer

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

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.