trigger code:
delimiter //
drop trigger OnlineSystem.on_address_disabled//
create trigger OnlineSystem.on_address_disabled
after update on AddressBook
for each row
begin
declare m_noAddressId int default NULL;
/* If an address is disabled, then all shopping cart lines pointing to that address must point */
/* to 'No Address' value */
if ( NEW.addressStatus = 'disabled' ) then
/* Get the 'No Address' row id */
select
`id` into `m_noAddressId`
from
`AddressBook`
where
lcase(`name`) = 'no address';
update `ShoppingCartProduct`
set
`AddressBookId` = `m_noAddressId`
where
`AddressBookId` = NEW.id;
end if;
end;
//
Check that the user you are using to create the trigger has the necessary priviledges - I'm not sure exactly what is required, but I seem to remember that there is an optional DEFINER clause of the CREATE TRIGGER syntax, where you can put the username whose priviledges will be used at run-time.
As I say, I'm not completely sure, but I think - judging by the error - that the problem lies somewhere aroung there.
the definer clause is not optional from 0.17 and it cannot accept current user or current_user values instead it requires only valid user names like root@localhost or 'root'@'localhost'