In the last episode (Jul 07), Marcel Grandemange said:
> I hope someone can assist me. I have a freeradius server running off
> mysql. Now I would like to use triggers to negate some of the traffic
> logged within it. I tried to use following as trigger..
> Create Trigger ftp
> BEFORE UPDATE ON radacct
> FOR EACH ROW
> UPDATE radacct SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets) WHERE NASPortId=21;
> UPDATE radacct SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets) WHERE
> And Many variants of that but only ends up locking the db in someway with
> messages such as.
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: Cannot store result
> Tue Jul 7 23:34:12 2009 : Error: rlm_sql_mysql: MySQL error 'Can't update table
> 'radacct' in stored function/trigger because it is already used by statement which invoked
> this stored function/trigger.'
You don't want to UPDATE the table, since as you have seen it will cause an
error. The only row you can change in the table being modified in a trigger
is the active row itself, and you must change its values by SETting
IF NEW.NASPortID = 21 THEN
SET NEW.AcctInputOctets=(0 - NEW.AcctInputOctets);
SET NEW.AcctOutputOctets=(0 - NEW.AcctOutputOctets):