mysql trigger display an error

Question

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

Dejv · Accepted Answer · 2013-08-27 12:34:53Z

1

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;
...

answered Aug 27, 2013 at 12:34

Dejv

9442 gold badges14 silver badges31 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

wchiquito · Accepted Answer · 2013-08-27 12:55:46Z

0

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.

edited Aug 27, 2013 at 12:55

answered Aug 27, 2013 at 12:28

wchiquito

16.7k2 gold badges37 silver badges46 bronze badges

Collectives™ on Stack Overflow

mysql trigger display an error

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related