unable to create trigger on mysql 5.0.18[solved]

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

unable to create trigger on mysql 5.0.18[solved]

Post 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
Last edited by raghavan20 on Tue May 09, 2006 3:58 pm, edited 1 time in total.
GM
Forum Contributor
Posts: 365
Joined: Wed Apr 26, 2006 4:19 am
Location: Italy

Post 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.
User avatar
raghavan20
DevNet Resident
Posts: 1451
Joined: Sat Jun 11, 2005 6:57 am
Location: London, UK
Contact:

Post 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'
Post Reply