MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE `leads` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lead` INT NOT NULL,
    `status` VARCHAR(100) NOT NULL,
    `searchAddressId` INT,
  CONSTRAINT `leads-data_ibfk_1` FOREIGN KEY (`lead`) REFERENCES `leads` (`id`) ON DELETE CASCADE
) ENGINE=INNODB;
INSERT INTO `leads` (`lead`, `status`, `searchAddressId`) VALUES
  (1, 'address-search',     1),
  (2, 'fresh', 1),
  (3, 'completed', 1),
  (4, 'suspended',     1);
SELECT * FROM leads;

CREATE TABLE `leads-data` (
    `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    `lead` INT NOT NULL,
    `status` VARCHAR(100) NOT NULL,
    `searchAddressId` INT
) ENGINE=INNODB;
INSERT INTO `leads-data` (`lead`, `status`, `searchAddressId`) VALUES
  (1, 'fresh', 1),
  (1, 'address-search', 1),
  (1, 'address-search', 1),
  (2, 'fresh',     1),
  (2, 'fresh', 1),
  (3, 'fresh', 1),
  (3, 'completed', 1),
  (4, 'fresh', 1),
  (4, 'completed', 1),
  (4, 'suspended', 1);
SELECT * FROM `leads-data`

Records: 4  Duplicates: 0  Warnings: 0




id
lead
status
searchAddressId


1
1
address-search
1

2
2
fresh
1

3
3
completed
1

4
4
suspended
1



Records: 10  Duplicates: 0  Warnings: 0




id
lead
status
searchAddressId


1
1
fresh
1

2
1
address-search
1

3
1
address-search
1

4
2
fresh
1

5
2
fresh
1

6
3
fresh
1

7
3
completed
1

8
4
fresh
1

9
4
completed
1

10
4
suspended
1


CREATE FUNCTION `calcFreshLeadSearchId`(leadId INT) RETURNS INT
BEGIN
	DECLARE lastSearchAddressID INT DEFAULT NULL;
	DECLARE lastStatus VARCHAR(100) DEFAULT '';
	
    SELECT
        `searchAddressId`,
        `status`
	INTO
		lastSearchAddressID,
        lastStatus
	FROM `leads`
	WHERE `id` = leadId;
    
    -- if there is no status previous it's a first time entry
    IF lastSearchAddressID IS NULL THEN
		RETURN 1;
	END IF;
    
	-- if the previous entry was fresh but no searchAddressId has been assigned
	IF lastStatus = 'fresh' AND lastSearchAddressID IS NULL THEN
		RETURN 1;
    END IF;
    
	-- if there is a previous status and a searchAddressId has already been assigned
    IF lastStatus != NULL AND lastSearchAddressID > 0 THEN
		RETURN lastSearchAddressID + 1;
	END IF;
    
    -- if last entry has already been assigned then we maintain the 1 as searchAddressId
    If lastStatus = 'fresh' AND lastSearchAddressID = 1 THEN
      RETURN 1;
	END IF;
END
\

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '\' at line 35


CREATE /* DEFINER=`admin`@`` */ TRIGGER `leads-data_BEFORE_INSERT` BEFORE INSERT ON `leads-data` FOR EACH ROW BEGIN
DECLARE newSearchAddressId INT;

IF NEW.status = 'fresh' THEN
	SELECT calcFreshLeadSearchId(NEW.lead)
	INTO newSearchAddressId;
    
	SET NEW.searchAddressId = newSearchAddressId;
END IF;

IF NEW.status = 'address-search' THEN
	SELECT calcAddressSearch_searchAddressId(NEW.lead)
    INTO newSearchAddressId;
    
    SET NEW.searchAddressId = newSearchAddressId;
END IF;
END
CREATE /* DEFINER=`admin`@`` */ TRIGGER `leads-data_AFTER_INSERT` AFTER INSERT ON `leads-data` FOR EACH ROW 
BEGIN

UPDATE `leads` SET
    status = NEW.status,
    searchAddressId = NEW.searchAddressId
WHERE `id` = NEW.lead;

END
INSERT INTO `leads-data` VALUES (NULL, 1, 'fresh', NULL);

SELECT * FROM `leads-data`;

FUNCTION fiddle.calcFreshLeadSearchId does not exist
id	lead	status	searchAddressId
1	1	fresh	1
2	1	address-search	1
3	1	address-search	1
4	2	fresh	1
5	2	fresh	1
6	3	fresh	1
7	3	completed	1
8	4	fresh	1
9	4	completed	1
10	4	suspended	1