2 Replies - 5919 Views - Last Post: 18 March 2013 - 12:22 PM Rate Topic: -----

#1 dreamincodehamza  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: -12
  • View blog
  • Posts: 330
  • Joined: 12-September 08

trigger issue

Posted 17 December 2012 - 02:19 AM

my triggeR
DELIMITER // 
	CREATE TRIGGER test
	FOR EACH ROW 
	
BEGIN
 
 #CALL update_pro();
 IF NEW.username != 'admin' THEN
  UPDATE users SET new.username = 'admin' WHERE old.id = '4';
 END IF;
 
END
	END//
DELIMITER ;

# AFTER UPDATE ON products 
# FOR EACH ROW







getting error
site:forums.mysql.com 1442 - Can't update table 'users' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.mysql>

Pleas help ,,
any solution

Is This A Good Question/Topic? 0
  • +

Replies To: trigger issue

#2 e_i_pi  Icon User is offline

  • = -1
  • member icon

Reputation: 789
  • View blog
  • Posts: 1,676
  • Joined: 30-January 09

Re: trigger issue

Posted 17 December 2012 - 07:41 PM

AFAIK, it's not possible to update a row of a table that is calling the trigger, otherwise the trigger can become recursive.

It looks like all you're doing is setting the username to 'admin' when the id is '4'. Is it necessary to do this as a trigger? Why don't you just use a run-once script?
Was This Post Helpful? 0
  • +
  • -

#3 dreamincodehamza  Icon User is offline

  • D.I.C Regular
  • member icon

Reputation: -12
  • View blog
  • Posts: 330
  • Joined: 12-September 08

Re: trigger issue

Posted 18 March 2013 - 12:22 PM

View Poste_i_pi, on 17 December 2012 - 07:41 PM, said:

AFAIK, it's not possible to update a row of a table that is calling the trigger, otherwise the trigger can become recursive.

It looks like all you're doing is setting the username to 'admin' when the id is '4'. Is it necessary to do this as a trigger? Why don't you just use a run-once script?

basically i just want to do this using trigger because as we know triggers activated on add, update, delete
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1