I’m having trouble creating a trigger in my MySQL database and I’m encountering an error. The purpose of this trigger is to execute after new records are inserted into the radacct table.
Here is the trigger code I’m using:
CREATE TRIGGER `chk_mac_after_insert` AFTER INSERT ON `radacct`
FOR EACH ROW BEGIN
-- Verify if the user's MAC address is already in the radcheck table
SET @mac = (SELECT count(*) FROM radcheck WHERE username = NEW.username AND
attribute = 'Calling-Station-ID');
-- If there are no existing entries for this MAC, insert it into the radcheck table
IF (@mac = 0) THEN
INSERT INTO radcheck (username, attribute, op, value) VALUES
(NEW.username, 'Calling-Station-ID', ':=', NEW.callingstationid);
-- Update the users table with the MAC address
UPDATE users SET mac = NEW.callingstationid WHERE username = NEW.username;
-- Trim the MAC to identify the vendor
SET @mactrim = (SELECT LEFT(mac, 8) FROM users WHERE username = NEW.username);
-- Retrieve the vendor name associated with the MAC address
SET @macvendor1 = (SELECT vendor FROM macdb WHERE oui = @mactrim);
-- Update the users table with the vendor name
UPDATE users SET macvendor = @macvendor1 WHERE username = NEW.username;
END IF;
END
However, I’m receiving the following error:
#1064 - 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 3
Can anyone help me understand what might be wrong with my trigger syntax? I’m not able to pinpoint the issue.
classic delimiter issue. mysql stops at the 1st semicolon, so it sees an error. try wrapping your trigger code with DELIMITER $$ at the start and DELIMITER ; at the end to fix this.
Yeah, the delimiter issue is real, but honestly? MySQL triggers for this stuff get messy fast. You’re basically building a complex workflow inside your database.
I’ve done similar MAC address tracking before. Database triggers become a pain to debug and maintain, especially with multiple table updates and vendor lookups.
What worked way better was pulling this logic out of the database completely. Instead of triggers, I used Latenode to watch the radacct table for new inserts. When a new record shows up, it runs the same checks and updates but with actual error handling and logging.
The workflow checks if the MAC exists in radcheck, inserts if needed, updates the users table, trims the MAC for vendor lookup, and updates vendor info. Same steps, just way easier to modify and troubleshoot.
You also get retry logic if anything fails - triggers don’t give you that. And adding notifications or other integrations later is simple.
Triggers have their place, but complex business logic isn’t it. Check out Latenode’s database monitoring: https://latenode.com
You’re getting that syntax error because of how MySQL handles delimiters in triggers. MySQL normally uses semicolons to end statements, but your trigger has semicolons inside it - this confuses the parser. You need to change the delimiter before creating the trigger, then switch it back: DELIMITER // CREATE TRIGGER chk_mac_after_insertAFTER INSERT ONradacct FOR EACH ROW BEGIN -- your existing trigger code here END// DELIMITER ;. This makes MySQL use // temporarily, so the semicolons inside your trigger won’t be treated as statement endings. I’ve hit this same issue with stored procedures and triggers before.
The delimiter confusion is causing your error, but there’s another syntax issue that’ll bite you later. Those parentheses around your SELECT statements in variable assignments? You don’t need them and they can cause weird behavior in MySQL triggers. Instead of SET @mac = (SELECT count(*) FROM radcheck WHERE...); just use SELECT count(*) INTO @mac FROM radcheck WHERE...; - it’s clearer and more reliable. Same goes for @mactrim and @macvendor1. I’ve seen triggers fail silently or give inconsistent results with the parentheses approach, especially when you’ve got JOINs or subqueries. The INTO syntax is way more explicit and prevents parsing issues that won’t show up until you’re deep in testing.