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_ur
SET direct_competitor = 0
WHERE data_whitepapers_ranked_ur
I wrote the following trigger but it is not working.
Trigger
---------------
DROP TRIGGER `Update_ranked_pages`;
CREATE DEFINER=`root`@`localhost`
FOR EACH ROW BEGIN
UPDATE
data_whitepapers_ranked_ur
SET data_whitepapers_ranked_ur
WHERE data_whitepapers_ranked_ur
Can someone give me a hand with this Trigger
Thanks
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_ur
SET direct_competitor = 0
WHERE data_whitepapers_ranked_ur
I wrote the following trigger but it is not working.
Trigger
---------------
DROP TRIGGER `Update_ranked_pages`;
CREATE DEFINER=`root`@`localhost`
FOR EACH ROW BEGIN
UPDATE
data_whitepapers_ranked_ur
SET data_whitepapers_ranked_ur
WHERE data_whitepapers_ranked_ur
Can someone give me a hand with this Trigger
Thanks
ASKER
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.
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.
The LIKE comparison note: If the data_whitepapers_ranked_ur
Possible comparison could be:
data_whitepapers_ranked_ur
but it really depends on your data.
Possible comparison could be:
data_whitepapers_ranked_ur
but it really depends on your data.
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;
ENDASKER
I have tried the following and the second table does not update.
CREATE DEFINER=`root`@`localhost`
FOR EACH ROW UPDATE data_whitepapers_ranked_ur
SET data_whitepapers_ranked_ur
WHERE data_whitepapers_ranked_ur
It does not work. The id and domain dont change so I changed it to OLD
Ideally if the data_whitepapers.direct_co
How do you add the if statement to this clause?
CREATE DEFINER=`root`@`localhost`
FOR EACH ROW UPDATE data_whitepapers_ranked_ur
SET data_whitepapers_ranked_ur
WHERE data_whitepapers_ranked_ur
It does not work. The id and domain dont change so I changed it to OLD
Ideally if the data_whitepapers.direct_co
How do you add the if statement to this clause?
Can you post your table schema.
As you may see MySQL syntax is not my friend...
BUT what values are in data_whitepapers.id and data_whitepapers_ranked_ur
I would guess the id represents unique ID of the row in each table (as the common standard) and data_whitepapers_ranked_ur
Thus data_whitepapers_ranked_ur
I would expect data_whitepapers_ranked_ur
But maybe I am mistaken...
BUT what values are in data_whitepapers.id and data_whitepapers_ranked_ur
I would guess the id represents unique ID of the row in each table (as the common standard) and data_whitepapers_ranked_ur
Thus data_whitepapers_ranked_ur
I would expect data_whitepapers_ranked_ur
But maybe I am mistaken...
ASKER
I have attached the two tables
sample.sql
sample.sql
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
If this column would connect the two tables then it should also work for the question author.
ASKER
Hi Julian
That worked for me.
Thanks for the help.
That worked for me.
Thanks for the help.
You are welcome.
Try this
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.