Another Triggers question...

[Jim R]

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.

 

 

[requinix]

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.

[Jim R]

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?

[requinix]

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.

[Jim R]

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.

[requinix]

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...".

[Jim R]

I'll look it over and report back.  Thanks.  

Edited September 5, 2017 by Jim R

[Jim R]

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".  

[Jim R]

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))