Trigger in MySQL

[Marc Vanzegbroeck]

Hi,

I use this trigger in SQLite

Code: Select all  Expand view

CREATE TRIGGER planning_update UPDATE ON planning BEGIN UPDATE refresh SET nr = nr + 1 where ID = 1;END;

but it doesn't work in MySQL. Does anyone know why?
'planning' and 'refresh' are tables in my SQL-database.

Thanks,Marc

[Daniel Garcia-Gil]

Hello

is showing some error?

[Kleyber]

Hi,

I use this trigger in SQLite

Code: Select all  Expand view

CREATE TRIGGER planning_update UPDATE ON planning BEGIN UPDATE refresh SET nr = nr + 1 where ID = 1;END;

but it doesn't work in MySQL. Does anyone know why?
'planning' and 'refresh' are tables in my SQL-database.

Thanks,Marc

Marc,

Try this:

Code: Select all  Expand view

CREATE TRIGGER planning_update AFTER UPDATE ON planning
    FOR EACH ROW BEGIN
       UPDATE planning SET nr = nr + 1 where ID = 1;
    END;
 

I hope this helps.

[Marc Vanzegbroeck]

Kleyber,

Thank you for the info, but how do I have to do those 4 lines?

oSQL:query('CREATE TRIGGER planning_update AFTER UPDATE ON planning')
oSQL:query('FOR EACH ROW BEGIN')
oSQL:query('UPDATE refresh SET nr = nr + 1 where ID = 1;')
oSQL:query('END;')

doesn't seems to work.
Do I put these lines on 1 line?

Thanks,
Marc

[Daniel Garcia-Gil]

Hello

do in 1 line... what class are you using to manager mysql?

in TDolphin is:
oServer:Execute( "CREATE TRIGGER planning_update AFTER UPDATE ON planning FOR EACH ROW BEGIN UPDATE planning SET nr = nr + 1 where ID = 1; END" )

in TMysql (from harbour ) maybe is
mysql_query( oServer:nSocket, "CREATE TRIGGER planning_update AFTER UPDATE ON planning FOR EACH ROW BEGIN UPDATE planning SET nr = nr + 1 where ID = 1; END" )

[Marc Vanzegbroeck]

Daniel,

Until I upgrade my FWH, i use TMySQL, because by version of FWH still use xHarbour 0.99.71 which is not compatible with TDolphin.

The function mysql_query() is not in the library.
I tested it with oSQL:Query( "CREATE TRIGGER planning_update AFTER UPDATE ON planning FOR EACH ROW BEGIN UPDATE refresh SET nr = nr + 1 where ID = 1; END;;" ) , and that is working fine!!

I have still now an other question. How can I know if the trigger exist? In SQLite al always executed this command, and if it exist, it doesn't do anything, MYSQL (MariaDB) give an error multiple triggers with the same action is not supported.
Regards,
Marc

[Kleyber]

Marc,

The way I remember now is using the SHOW TRIGGERS command and check the resulting array.

[Marc Vanzegbroeck]

Kleyber,

It's working fine with the SHOW TRIGGERS command.

Thanks,
Marc