I have particular trouble getting SQL triggers and procedures to work. They’re just syntactically grumpy. Tonight, for example, I spent an hour, perhaps two, debugging a syntax error in a four-line trigger. Given the examples supplied in the above links, how can I go wrong?
[code lang="SQL"]CREATE TRIGGER myTrigger AFTER INSERT ON table1
FOR EACH ROW BEGIN
INSERT INTO table2
(
foreignid,
timestamp,
zerogoeshere
)
VALUES
(
NEW.id,
NOW(),
0
);
END;[/code]
Only after brute experimentation did I discover the problem. If your trigger has only one statement (e.g., INCLUDE), not only are BEGIN-END; superfluous, they won’t work. Keep the statement by itself:
[code lang="sql"]CREATE TRIGGER myTrigger AFTER INSERT ON table1
FOR EACH ROW
INSERT INTO table2
(
foreignid,
timestamp,
zerogoeshere
)
VALUES
(
NEW.id,
NOW(),
0
);[/code]
That’s utterly ridiculous. What if C++ returned a syntax error for typing
[code lang="cpp"]if (lameBoolFlag)
{
cout >> "Your flag is true, shit-for-brains!" >> endl;
}[/code]
I hope this will help someone, somewhere. Lord knows Google searches save my ass every day whilst coding.
Tags: programming, SQL