Me and my team need a simple sql trigger to update one column (column2) of a table when another (column1) is updated. Both columns are in the same table, and this trigger will be going in our AWS RDS.
Here is my code:
CREATE TRIGGER recalc_trigger ON {{db_name}}.{{table_name}}
AFTER UPDATE
AS
IF UPDATE({{column2}})
BEGIN
SET NOCOUNT ON;
UPDATE {{db_name}}.{{table_name}}
SET {{column1}} = (deleted.{{column1}}/(1 + deleted.{{column2}})) * (1 + inserted.{{column2}})
WHERE {{table_primary_key}} = inserted.{{table_primary_key}};
END
From all the examples I have seen online this should work, but instead I get this error:
SQL Error [1064] [42000]: 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 'ON {{db_name}}.{{table_name}} AFTER UPDATE AS IF UPDATE({{column2}}' at line 1
Can anyone point out to me what I am doing wrong?
The syntax is that the action is named before the table.
Also you must use FOR EACH ROW in MySQL, not AS.
And if you use compound statements like IF, you must put the body of the trigger in a BEGIN...END block.
Incorrect:
CREATE TRIGGER recalc_trigger ON {{db_name}}.{{table_name}}
AFTER UPDATE
AS
IF ...
Correct:
CREATE TRIGGER recalc_trigger
AFTER UPDATE
ON {{db_name}}.{{table_name}}
FOR EACH ROW
BEGIN
IF ...
END
Also the {{db_name}} identifier is not valid, unless you delimit it. I assume this is just anonymized in your example, or else it's part of a template or something.
The syntax you tried makes me think you're accustomed to trigger syntax of Microsoft SQL Server. You should study the syntax documentation of MySQL, because it's different from Microsoft.
For example, in MySQL use OLD.<column> and NEW.<column> not deleted and inserted. Also SET NOCOUNT ON isn't part of MySQL's dialect.