update trigger AFTER UPDATE not working

HI
I have two tables.
I want 1 column updated in the second table when I update the 1st table.
Here is the logic

Table 1
------------------
UPDATE data_whitepapers
SET direct_competitor = 0
WHERE id = 44

Table 2
table 2 uses the domain column value from table 1 and the id from table 1
------------------
UPDATE data_whitepapers_ranked_urls
SET direct_competitor = 0
WHERE data_whitepapers_ranked_urls.Page like 'domain.com' AND data_whitepapers_ranked_urls.id = 44

I wrote the following trigger but it is not working.

Trigger
---------------
DROP TRIGGER `Update_ranked_pages`;

CREATE DEFINER=`root`@`localhost` TRIGGER `Update_ranked_pages` AFTER UPDATE ON `data_whitepapers`
FOR EACH ROW BEGIN
UPDATE
data_whitepapers_ranked_urls
SET data_whitepapers_ranked_urls.direct_competitor = 0
WHERE data_whitepapers_ranked_urls.Page like NEW.domain AND data_whitepapers_ranked_urls.id = NEW.id;

Can someone give me a hand with this Trigger

Thanks
matthewdacruzAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Why are you using LIKE on the domain field - if there are no wild cards?

Try this
CREATE 
TRIGGER `Update_ranked_pages` AFTER UPDATE 
ON `data_whitepapers`
FOR EACH ROW BEGIN
	UPDATE data_whitepapers_ranked_urls
	SET data_whitepapers_ranked_urls.direct_competitor = 0
	WHERE data_whitepapers_ranked_urls.Page = new.domain AND data_whitepapers_ranked_urls.id = new.id;
END

Open in new window


IF that does not work then take the update statement out of there, substitute the new.field for actual values and test that it works.
0
matthewdacruzAuthor Commented:
Hi Julian
The second table holds a full url.
The domain in the is either www.domain.com or domain.com in the 1st table.
I was not sure how to add wildcards to the trigger.
What would you suggest?

In an ideal situation if the value of direct_competitor = 0 Then I want the trigger to update all the rows in the second table to 0 that contain the domain of the first table.
0
pcelbaCommented:
The LIKE comparison note: If the data_whitepapers_ranked_urls.Page contains the whole web Page URL and NEW.domain just the domain then you cannot expect any match.

Possible comparison could be:
data_whitepapers_ranked_urls.Page LIKE '%'+NEW.domain+'%'
but it really depends on your data.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Julian HansenCommented:
Pretty sure LIKE '%' + new.domain + '%' won't work - you need to use CONCAT

CREATE 
TRIGGER `Update_ranked_pages` AFTER UPDATE 
ON `data_whitepapers`
FOR EACH ROW BEGIN
	UPDATE data_whitepapers_ranked_urls
	SET data_whitepapers_ranked_urls.direct_competitor = 0
	WHERE data_whitepapers_ranked_urls.Page LIKE CONCAT('%',new.domain,'%') AND data_whitepapers_ranked_urls.id = new.id;
END

Open in new window

0
matthewdacruzAuthor Commented:
I have tried the following and the second table does not update.

CREATE DEFINER=`root`@`localhost` TRIGGER `Update_ranked_pages` AFTER UPDATE ON `data_whitepapers`
FOR EACH ROW UPDATE data_whitepapers_ranked_urls
SET data_whitepapers_ranked_urls.direct_competitor = 0
WHERE data_whitepapers_ranked_urls.Page = CONCAT('%',OLD.domain,'%') AND data_whitepapers_ranked_urls.id = OLD.id;

It does not work. The id and domain dont change so I changed it to OLD
Ideally if the data_whitepapers.direct_competitor = 0 then the update should run.

How do you add the if statement to this clause?
0
Julian HansenCommented:
Can you post your table schema.
0
pcelbaCommented:
As you may see MySQL syntax is not my friend...

BUT what values are in  data_whitepapers.id  and  data_whitepapers_ranked_urls.id ?

I would guess the id represents unique ID of the row in each table (as the common standard) and data_whitepapers_ranked_urls contains more rows for each corresponding row in the data_whitepapers table...

Thus data_whitepapers_ranked_urls.id = OLD.id  cannot work.  (OLD or NEW does not matter here.)

I would expect data_whitepapers_ranked_urls  contains another column which connects its rows to its parent data_whitepapers table.

But maybe I am mistaken...
0
matthewdacruzAuthor Commented:
I have attached the two tables
sample.sql
0
Julian HansenCommented:
This works for me on those tables
CREATE TRIGGER `Update_ranked_pages` 
AFTER UPDATE ON `data_whitepapers` 
FOR EACH ROW 
	UPDATE data_whitepapers_ranked_urls
	SET data_whitepapers_ranked_urls.direct_competitor = 0
	WHERE data_whitepapers_ranked_urls.Page LIKE CONCAT('%',new.domain,'%') AND data_whitepapers_ranked_urls.id = new.id;

Open in new window

Screen shot before update is run
Before updateScreen shot of data_whitepapers after update
s27.jpgScreen shot of data_whitepapers_ranked_urls after update is run on data_whitepapers
After update
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
pcelbaCommented:
Yes, it must work if the `id` column represents the connection between the two tables... But this is still not confirmed.

If this column would connect the two tables then it should also work for the question author.
0
matthewdacruzAuthor Commented:
Hi Julian
That worked for me.
Thanks for the help.
0
Julian HansenCommented:
You are welcome.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.