Dealing with Triggers...

[Jim R]

I have set up a Trigger...

 

update a_players p  

join wp_terms t

set p.wpID = t.term_id

where t.slug = p.wpSlug

 

 

...and it works.  These are relatively new data tables, and I had intended on the trigger to just deal with new entries (as Tags posted via WordPress).  However, it ended up updating previously applicable rows, which didn't anticipate.  It makes sense, but before I go live, I'm going create term_id / wpID matches for all previous rows.  

 

 

As of now, wp_terms is just 42 rows, but it will grow a great deal once the site actually goes live.  a_players currently has 150 rows but it going to approach about 1,000 rows before I go live, growing around 250 rows per year.  

 

 

Should I narrow my trigger down?  

 

If I should narrow it down to just deal with new entries, what do I need to add?  

 

If It's going to check every row in both tables, will that bog down my site?

 

 

Thank you. 

Edited September 5, 2017 by Jim R

[requinix]

If you made an INSERT trigger then it applies to new records. If you made an UPDATE trigger then it applies to changed records.

 

If you have a trigger that's supposed to "deal with new entries" but it's "updating previously applicable rows" then your trigger is wrong.

[Jim R]

If you made an INSERT trigger then it applies to new records. If you made an UPDATE trigger then it applies to changed records.

 

If you have a trigger that's supposed to "deal with new entries" but it's "updating previously applicable rows" then your trigger is wrong.

 

The trigger is intended to Update a row in a_players After each Inserted row to wp_terms.  

 

It's set up as:

 

Action Time:  After

Event:  Insert

 

update a_players p  

join wp_terms t

set p.wpID = t.term_id

where t.slug = p.wpSlug

 

It did what I wanted with the new entry, so for my NEEDS it's working.  However, it also appears to checking the entirety of one table to another, rather than just deal with new entries.  Hence my questions.  

 

 

I'm looking for the answers to my questions for the sake of database efficiency.  

 

 

Thank you.

[requinix]

Well, what you've written, yes it will check every single row in both tables. The question is whether you want it to do that or whether you want it to only work with the added record(s)?

 

This whole thing doesn't quite make sense to me. Why are there a_players records with a wpSlug that does not exist in wp_terms? When creating or updating a_players, shouldn't you be enforcing an existing wp_terms first (perhaps by automatically creating it)?

And which table is the trigger on?

[Jim R]

I don't need to check the entire data table, but it only matters if it's going to bog down the database as each table grows.  My guess is it will, which is why I'm asking for a better way to create the trigger.  The trigger is set up on the wp_terms table since that's where the Tags (term_id) are created.  It updates the a_players, so I can link the information in the two data tables.  

 

 

The rows in wp_terms are created by WordPress as a new Tag or term (Category, etc) is created.  The rows in a_players are created by me, separately.  I'll have more rows in a_players than Tags in wp_terms.   Each year I'll add about 250 player names (freshmen), but each of those players won't be written about or have analysis.  As we write about one of them and Tag them, I want to link that association from wp_terms to a_players.  I can do it manually, but I'd rather not.

 

 

Again, hence my questions above.  Will it bog down my site as it grows?  (If not, then no worries.)  If it will, what do I need to add to the Trigger to make sure it just checks new rows?

 

(It's doing what I need it to do.  I'd like to make sure it's efficient.)

[requinix]

The question about it "bogging down" the database is secondary. The main problem is that the trigger does not do what you want it to do, namely update an existing player given a new tag. So fix that. Then you can think about performance, but at that point it won't even be an issue.

 

Instead of looking at wp_terms, look at NEW. It's a "table" containing the new values added to the parent.

[Jim R]

I does what I need it to do, associate the player a new tag.  That is working just fine.  It's doing it, however, by cross checking the entirety of both data tables instead of just dealing new entries.  

 

I looked up NEW, but anything I have tried has produced syntax errors.  

update a_players p  
join wp_terms t

if NEW.t.term_id then

set p.wpID = t.term_id
where t.slug = p.wpSlug;

end if;

I've also tried it with NEW.term_id.

[requinix]

Yeah, that's not even close to valid syntax. I linked the docs in the other thread.

 

If your original query was

update a_players p  
join wp_terms t
set p.wpID = t.term_id
where t.slug = p.wpSlug

and NEW.term_id and NEW.slug are both things, then

update a_players set wpID = NEW.term_id where wpSlug = NEW.slug

Since wp_terms.term_id is an auto_increment-ed primary key you'll have to run this AFTER the insert so you can get its value.

 

I does what I need it to do, associate the player a new tag.

If you describe it that simply, yes. What I'm saying is that if you describe it precisely, as we've both done a few times now, it no longer is what you want. The end result is correct but the process to get there is not. Edited September 5, 2017 by requinix
update query without "p."

[Jim R]

Should I get rid of the table notation?  

 

NEW.term_id  instead of NEW.t.term_ID?

[requinix]

Should I get rid of the table notation?

The two "p." bits? Yes.

 

NEW.term_id  instead of NEW.t.term_ID?

No. Why should it? NEW is neither a database nor a table. It's a thing that has variables inside it. NEW.term_id is a variable.

[Jim R]

 

No. Why should it? NEW is neither a database nor a table. It's a thing that has variables inside it. NEW.term_id is a variable.

 

Because it's common syntax to provide a table designation when querying from multiple tables.  

 

I used this for the trigger:

update a_players 
join wp_terms
set NEW.wpID = NEW.term_id
where slug = wpSlug

It wouldn't let me save it and got this error:  MySQL said: Unknown column 'wpID' in 'NEW'

[requinix]

Because it's common syntax to provide a table designation when querying from multiple tables.

NEW is not a table.

 

I used this for the trigger:

update a_players 
join wp_terms
set NEW.wpID = NEW.term_id
where slug = wpSlug

It wouldn't let me save it and got this error:  MySQL said: Unknown column 'wpID' in 'NEW'

 

Right. Because NEW.wpID is not a thing. In fact the whole query is wrong. Look at what I wrote earlier.

[Jim R]

Damn it...my bad.  I didn't see that you had put your own code up there, and it works.  (I deleted a tagID to see if would re-update it, and it didn't.)  

 

 

I'm still struggling on where to use NEW, as noticeable in the other topic, but I definitely see the logic in what you put up there.  

 

 

Thank you.