so I want to calculate the factorial of a user entered number. I need to do this with a WHILE loop in a trigger. I know this method is very counterintuitive, but it is how we've been instructed to do it. What I've come up with is that the user will enter their data in a table of the following format:

create table factorial(
    n int not null,
    fact int default 1,
    primary key(n));

The user will enter just the number 'n', following which a trigger will activate which will calculate the factorial and insert it in the corresponding 'fact' cell. The trigger I've come up with is this:

delimiter //
create trigger fact_calc
     before insert on factorial for each row
     begin
     declare f int;
     set f=1;
     declare c int;
     set c=1;
     while(c<=new.n)
     do
     set f=f*c;
     end while;
     insert into factorial(fact) values(f);
     end;
     //

But this is throwing a syntax error at the declare c int; line and I can't seem to figure out why. Any help would be appreciated.

I'm on a MariaDB 10.6 client running on Windows 10.

CodePudding user response：

DECLARE must appear before any other statements, so reorder your trigger.

However, there are other problems here.

• Firstly, you must use @f and @c for your variables or MySQl will assume you're referring to columns.
• Secondly, you're not incrementing @c, so your loop will run forever.
• Lastly, you can't do an INSERT from an INSERT trigger on the same table. Just use another SET.

Making these changes gives

delimiter //
create trigger fact_calc
     before insert on factorial for each row
     begin
     declare f int;
     declare c int;
     set @f=1;
     set @c=1;
     while(@c<=new.n)
     do
     set @f=@f*@c;
     set @c = @c 1;
     end while;
     set new.fact = @f;
     end;
     //

Reference

Note also that this is an INSERT trigger. If you update a row in your table this won't run. You may need a second similar UPDATE trigger.