Solved

problem wtih trigger create

Posted on 2013-12-18
2
318 Views
Last Modified: 2013-12-19
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
Comment
Question by:controlit
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 33

Accepted Solution

by:
snoyes_jw earned 500 total points
ID: 39727918
- 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
 

Author Closing Comment

by:controlit
ID: 39729035
and the answers should be, clear and perfect short
0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
There are cases when e.g. an IT administrator wants to have full access and view into selected mailboxes on Exchange server, directly from his own email account in Outlook or Outlook Web Access. This proves useful when for example administrator want…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question