Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

problem wtih trigger create

Posted on 2013-12-18
2
Medium Priority
?
320 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 2000 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

By, Vadim Tkachenko. In this article we’ll look at ClickHouse on its one year anniversary.
In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses

715 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