Insert Trigger mysql

I have 2 tables, the first one has columns (Id,Siteid,Article_link), the second (Id,Sitename), I want the siteid column to be inserted automatically when inserting into the first table, for example, if in the second table there is a filled line (3,https://stackoverflow.com/) when I add to the first one (1,https://stackoverflow.com/questions/ask) I want the siteid to be immediately set to 3 in the first table

Im trying write trigger like this:

AFTER INSERT ON links FOR EACH ROW BEGIN
UPDATE links SET siteid=id WHERE id IN(SELECT id FROM sites) and link REGEXP(SELECT sitename FROM sites);

But I can't access the table I'm inserting data into

1 Table Links

2 What i wanna

3 Table Sites

Solution

DEMO

CREATE TABLE sites (
  id INT AUTO_INCREMENT PRIMARY KEY,
  sitename VARCHAR(255) NOT NULL,
  UNIQUE (sitename)
  );
CREATE TABLE links (
  id INT AUTO_INCREMENT PRIMARY KEY,
  siteid INT,
  article_link VARCHAR(2048),
  FOREIGN KEY (siteid) REFERENCES sites (id)
  );

CREATE TRIGGER set_siteid
BEFORE INSERT ON links
FOR EACH ROW
SET NEW.siteid = (
  SELECT id
  FROM sites
  WHERE NEW.article_link LIKE CONCAT(sites.sitename, '%')
  );

INSERT INTO sites (sitename) VALUES 
  ('http://stackoverflow.com/'),
  ('http://mail.google.com/'),
  ('http://dba.stackoverflow.com/'),
  ('https://google.com/'),
  ('https://mail.google.com/');

INSERT INTO links (article_link) VALUES
  ('https://google.com/search?text=something'),
  ('http://mail.google.com/message=123.html'),
  ('http://stackoverflow.com/main.htm'),
  ('http://google.com/rules/main.html');

SELECT * FROM sites;
SELECT * FROM links;

id	sitename
3	http://dba.stackoverflow.com/
2	http://mail.google.com/
1	http://stackoverflow.com/
4	https://google.com/
5	https://mail.google.com/

id	siteid	article_link
1	4	https://google.com/search?text=something
2	2	http://mail.google.com/message=123.html
3	1	http://stackoverflow.com/main.htm
4	null	http://google.com/rules/main.html

fiddle