#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF r

[gmstrikker]

I need to run:

        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

To install a lib i found here on forum, but when i try, returns this error:

SQL query:




CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 )





MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF rank_id ON players
       BEGIN
           UPDATE players
             ' at line 2

What should i do?

 

[gmstrikker]

bump

 

[gmstrikker]

is anyone here know mysql and can help me? -

 

[ralke]

pheraps the parameter OF is not intended to do what you want

try to exequte this query

        CREATE TRIGGER `guilds_update`
            AFTER UPDATE ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

and then try with

      AFTER UPDATE OF rank_id

instead of

      AFTER UPDATE ON players

when reading the title of the post it seems that this
'OF r
cannot be done by AFTER UPDATE, i searched that and found this

MySQL: AFTER UPDATE Trigger

This MySQL tutorial explains how to create an AFTER UPDATE Trigger in MySQL with syntax and examples. An AFTER UPDATE Trigger means that MySQL will fire this trigger after the UPDATE operation is executed.

www.techonthenet.com

that confirm this

check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF rank_id ON players

I dont know which statement has to be used to updated booth tables at the same time, players and rank_id but lets see what information we can get by doing this. Hope this helps somehow!

 

[gmstrikker]

1

        CREATE TRIGGER `guilds_update`
            AFTER UPDATE ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

Error
SQL query: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]




CREATE TRIGGER `guilds_update`
            AFTER UPDATE ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 )





MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN
           UPDATE players
              SET guild_id = IFNULL( (
   ' at line 3

2

        CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 );
        END;

SQL query: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]




CREATE TRIGGER `guilds_update`
            AFTER UPDATE OF rank_id
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 )





MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'OF rank_id
       BEGIN
           UPDATE players
              SET guild_' at line 2

 

[ralke]

CREATE TRIGGER `guilds_update`
            AFTER UPDATE ON players
        BEGIN
            UPDATE players
               SET guild_id = IFNULL( (
                       SELECT guild_id
                         FROM guild_ranks
                        WHERE id = rank_id
                   )
                   , 0 )





MySQL said: [IMG alt="Documentation"]http://localhost/phpmyadmin/themes/dot.gif[/IMG]

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'BEGIN
           UPDATE players
              SET guild_id = IFNULL( (
   ' at line 3

Ok this give us more information, now we need to see what is the right syntax to use near 'BEGIN. To start solving this I'm lack of time, but I searched some usefull links:

Using WHERE

MySQL WHERE Clause: AND, OR, IN, NOT IN Query Example

MySQL Where clause' is used to query data from a database and also used with operators like 'OR', 'AND', IN, NOT IN.

www.guru99.com

CREATING DIFFERENT TRIGGERS

SQL Server CREATE TRIGGER

In this tutorial, you will learn how to use the SQL Server CREATE TRIGGER statement to create a new trigger in the database.

www.sqlservertutorial.net

MAKING AFTER UPDATE TRIGGERS

Oracle / PLSQL: AFTER UPDATE Trigger

This Oracle tutorial explains how to create an AFTER UPDATE Trigger in Oracle with syntax and examples. An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.

www.techonthenet.com

USING DIFFERENT KIND OF QUERIES

SQL y MySQL - Ejercicio 1 - Ejercicio resuelto con 1 tabla - AprendeAProgramar.com

Reading stuff like that can give you ideas of what you need to try, some examples could be:

CREATE TRIGGER `guilds_update`
AFTER UPDATE ON `players` WHERE `rank_id`<> 1;
BEGIN

or something like

AFTER UPDATE
SELECT `rank_id` FROM `players`

and reading the errors that appears after the running the query, will guide you to the source of the problem (with some google search ofc)

I've never studied much of this, the only experience I had with MySQL are OTServers, but I guess this information could help you. Good luck with it, i'll do more research to see if we can get it to work, regards!