Page 1 of 1

unable to create trigger on mysql 5.0.18[solved]

Posted: Tue May 09, 2006 6:21 am
by raghavan20
I am unable to create a trigger on mysql 5.0.18-standard-log but I was able to create a trigger using the same code on mysql 5.0.15-log.

Code: Select all

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;
//

error:

Code: Select all

definer not fully qualified

Posted: Tue May 09, 2006 8:54 am
by GM
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.

Posted: Tue May 09, 2006 3:57 pm
by raghavan20
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'