Reputation: 4455
i want to convert a field when a insertion takes place in one of my table.
check & ged's => check and geds.
DELIMITER $$
CREATE
TRIGGER `perma_limk_insertion` AFTER INSERT
ON `tbl_magazine`
FOR EACH ROW BEGIN
DECLARE magazine_name VARCHAR(100);
@magazine_name := REPLACE(FieldName,'&','and');
@magazine_name := REPLACE(FieldName,"'",'');
UPDATE tbl_magazine SET perma_link = magazine_name WHERE MAGAZINE_ID = NEW.MAGAZINE_ID;
END$$
DELIMITER ;
this is my trigger. but i got an error that
#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 '@magazine_name := REPLACE(FieldName,'&','and'); @magazine_name := REPLA' at line 7
if any one know about this. please help me
Thanks in advance
Upvotes: 0
Views: 50
Reputation: 16551
Use 13.7.4. SET Syntax.
...
SET @magazine_name := REPLACE(FieldName,'&','and');
SET @magazine_name := REPLACE(FieldName,"'",'');
...
When or where is define the variable: FieldName?
You should also check the logic. For example @magazine_name
is a 9.4. User-Defined Variables and magazine_name VARCHAR(100)
one 13.6.4.1. Local Variable DECLARE Syntax, are different variables.
In this case, it will assign NULL
to the column perma_link
, because it is the value of magazine_name
(never assigned), the variable is being assigned @magazine_name
.
Upvotes: 0
Reputation: 954
You need to change variable FieldName
to name of column you want to run function REPLACE on (in this case column name perma_link
).
Your code will be:
...
@magazine_name := REPLACE(perma_link,'&','and');
@magazine_name := REPLACE(perma_link,"'",'');
UPDATE tbl_magazine SET perma_link = magazine_name WHERE MAGAZINE_ID = NEW.MAGAZINE_ID;
...
Upvotes: 1