• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 327
  • Last Modified:

problem wtih trigger create

hello i have trigger

DELIMITER |

CREATE TRIGGER controlit AFTER INSERT ON

  tblregistros_buffer
  FOR EACH ROW BEGIN
    IF NEW.buffer  between 0 and 33 THEN

      update tblregistros_buffer SET NEW.idcolor=2;

END IF;
 END
|

DELIMITER ;



but after of insert show

#1442 - Can't update table 'tblregistros_buffer' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.

controlit  is database
tblregistros_buffer is table

i need that when i insert records in table tblregistros_buffer, save in column idcolor=2 with condition if

idcolor and buffer are of tblregistros_buffer
0
controlit
Asked:
controlit
1 Solution
 
snoyes_jwCommented:
- Make it a BEFORE instead of AFTER
- Don't use the UPDATE syntax, just a simple SET
DELIMITER |
CREATE TRIGGER controlit BEFORE INSERT ON tblregistros_buffer
FOR EACH ROW 
BEGIN
    IF NEW.buffer  between 0 and 33 THEN
      SET NEW.idcolor=2;
    END IF;
 END|

DELIMITER ;

Open in new window

0
 
controlitAuthor Commented:
and the answers should be, clear and perfect short
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now