Jump to content

Another Triggers question...


Jim R

Recommended Posts

Action Time:  After

Event:  Insert

 

update a_players

set NEW.name = concat(lower(NEW.nameFirst),'-',lower(NEW.nameLast))

 

 

 

I'm getting this error:

 

 

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

 

My Trigger first didn't have NEW in them.  I've looked that up, found usage of NEW, but even that still produced the error above.  Is it possible to Update a column of a row in real time as/after Inserting that new row?  

 

I'm trying to streamline data entry for my writers.

 

 

Link to comment
Share on other sites

1. Make the trigger fire before the insert. I mean, really, should be obvious.

2. Update NEW, not a_players.

 

Or better yet, get rid of "name" entirely. It's a computed value which means you have to worry about keeping it up to date all the time. Don't bother. If you need the "name" value then calculate it at that moment.

Link to comment
Share on other sites

I tried Before too, but I got the same error.  

 

 

As I noted, I'm trying to streamline the data entry process for my writers.  Some of which might be adding 30, 40 names at a time.  It also helps eliminate typos, which might disrupt the process after the names are submitted.  

 

 

Thoughts on having the Trigger update another table, then creating a Trigger on that table to update my main table?

Link to comment
Share on other sites

I tried Before too, but I got the same error.

Right. You have two problems, not one.

 

As I noted, I'm trying to streamline the data entry process for my writers.  Some of which might be adding 30, 40 names at a time.  It also helps eliminate typos, which might disrupt the process after the names are submitted.

And that has no impact on what I said. I'm talking about the technical implementation. Not what your users are doing in your application.

  

Thoughts on having the Trigger update another table, then creating a Trigger on that table to update my main table?

You're asking for my thoughts on avoiding the problem by making the whole thing even more complicated? Fix the trigger.
Link to comment
Share on other sites

Right. You have two problems, not one.

 

And that has no impact on what I said. I'm talking about the technical implementation. Not what your users are doing in your application.

  

You're asking for my thoughts on avoiding the problem by making the whole thing even more complicated? Fix the trigger.

 

I've posted this looking for help to fix the trigger.  If I could have resolved the error on my own, I wouldn't have posted it.

Link to comment
Share on other sites

Great. So can I assume you read the entirety of my first reply, especially the bit that talked about NEW, and forgot to ask about how to do that?

 

NEW is the row of data that will be inserted or updated into the source table. OLD similarly exists but for the old version of the data. An INSERT trigger uses NEW, DELETE uses OLD, and UPDATE uses both NEW and OLD.

If you want to modify the data being inserted into a table before it's inserted then you must modify the NEW data in a BEFORE trigger; triggers are not allowed to modify any tables involved in the original operation, and updating NEW in an AFTER will be too late.

 

NEW is not a table so you cannot UPDATE it, but it is like a variable (or rather, something that contains variables) so you would use SET with it.

 

The docs

 

Make the trigger run BEFORE the insert, then write a statement to SET the name value. The docs explain this in the paragraph starting "A column named with OLD is read only...".

Link to comment
Share on other sites

I had read that document earlier, and I'm still struggling on NEW syntax.  

 

 

Current trigger:

AFTER
INSERT on a_players

update a_playersset wpSlug = concat(lower(NEW.nameFirst),'-',lower(NEW.nameLast))
 
NEW in front nameFirst and nameLast but not wpSlug.  
 
I tried another version with NEW in front those variables, and the error I got that time was unknown column "nameFirst".  
Link to comment
Share on other sites

Found the answer in this line right here:

 

2/ Do not use UPDATE statement, use simple SET NEW.attribute.

 

The trigger that worked:

Action Time:  Before
Event:  Insert

set NEW.wpSlug = concat(lower(NEW.nameFirst),'-',lower(NEW.nameLast))
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.