deepu sankar
deepu sankar

Reputation: 4455

mysql trigger display an error

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

Answers (2)

wchiquito
wchiquito

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

Dejv
Dejv

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

Related Questions