Aurora MySQL 5.6 Serverless Trigger not working

Question

I have created a trigger in MYSQL Aurora (V1). It got created but when I run the insert Trigger is not firing. This same trigger works very well in Local MySQL Latest(8).

Is this because aurora because it is version 5.6 Aurora? Or is it because in serverless cluster we need to enable triggers (https://aws.amazon.com/premiumsupport/knowledge-center/rds-mysql-functions/) However, in the console it does not allow to create 5.6 Aurora parameter group.

Any ideas?

delimiter $$
CREATE TRIGGER `test_BEFORE_INSERT` BEFORE INSERT ON `test`
FOR EACH ROW BEGIN
  SET NEW.xxx_xx = (SELECT MAX(test_id) + 1 FROM test);
  IF NEW.`xxx_xx` IS NULL OR NEW.`xxx_xx` = '' THEN
        SET NEW.`xxx_xx` = 1;
  END IF;
END $$
delimiter ;

Thank Akina, it was not to do with trigger as such. Posting answer. — nidha

nidha nidha · Accepted Answer · 2021-03-01T21:25:01

The issue was not the trigger. When using MySQL workbench(client) and inserting it worked. However, when using Data API it seemed like it was not firing. I had xxx_xx defined as Not Null without a default value. For some reason this gave an error before trigger fired when using via Data API. Hence the fix was to allow NULL with a default value. Anyway the trigger will update the value on insert, hence it did cause any issues.

Aurora MySQL 5.6 Serverless Trigger not working

1 Answers