Using WHILE loop in BEFORE INSERT trigger

Question

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.

Tangentially Perpendicular · Accepted Answer · 2021-10-01 21:20:51Z

2

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.

edited Oct 1, 2021 at 21:20

answered Oct 1, 2021 at 21:00

Tangentially Perpendicular

5,4364 gold badges15 silver badges34 bronze badges

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

Collectives™ on Stack Overflow

Using WHILE loop in BEFORE INSERT trigger

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related