Is this the correct way to write trigger for my table in MySql?

DELIMITER $$

  CREATE TRIGGER before_insert_part_compatibility
  BEFORE INSERT ON part_compatibility
  FOR EACH ROW
  BEGIN
-- Step 1: Remove backslashes and extra quotes
SET NEW.model_id = REPLACE(REPLACE(NEW.model_id, '\\', ''), '\"', '"');

-- Step 2: Ensure model_id is a valid JSON string
SET NEW.model_id = JSON_QUOTE(REPLACE(REPLACE(NEW.model_id, '[', ''), ']', ''));

-- Step 3: Optional Logging for Debugging
INSERT INTO debug_log (log_message, created_at)
VALUES (CONCAT('Cleaned model_id: ', NEW.model_id), NOW());

END$$

 DELIMITER ;

jlrdw

1 year ago

Level 75

I am no expert on triggers. But here is one I used in the past that worked.

DELIMITER $$

USE `pbackdate`$$

DROP TRIGGER /*!50032 IF EXISTS */ `trzero_tonull`$$

CREATE
    /*!50017 DEFINER = 'root'@'localhost' */
    TRIGGER `trzero_tonull` BEFORE INSERT ON `pets` 
    FOR EACH ROW BEGIN
IF NEW.ownerid = 0 THEN
    SET NEW.ownerid = NULL;
END IF;
END;
$$

DELIMITER ;

But test yours in development to see if it works ok or not.

A good source: https://www.mysqltutorial.org/mysql-triggers/

Tray2

1 year ago

Level 73

@syed1980 I wrote a blog post about using triggers with laravel, it might shed some light.

https://tray2.se/posts/using-table-triggers-to-log-changes-in-your-database-tables

Please or to participate in this conversation.