powered by Jive Software

Huge problem. Plugin with Database-Script and Triggers (MYSQL)

Hello everybody,

I recently ran into a huge problem.

We developed a few plugins for OF.

We use MySQL and have about 30 custom Tables.

Now we wanted to use triggers and wanted to let the plugin system create those (by using the Database creation script as recommended in the Plugin Development guide).

However, the “DELIMITER” Function is not supported this way.

I have no way to perform multiple actions in a single trigger that way.

Here is an example of what WILL WORK

CREATE TRIGGER ofUser_TRIGGER_CREATE AFTER INSERT ON openfire.ofUser

FOR EACH ROW BEGIN

INSERT INTO paUserDetails (userID, firstname, lastname, street, zip, gender, nickname, image, city) VALUES (NEW.username, ‘’, ‘’, ‘’, ‘’, 0, ‘User’, ‘’, ‘’);

END;

And here is an example of what WILL NOT work

DELIMITER |

CREATE TRIGGER ofUser_TRIGGER_CREATE AFTER INSERT ON openfire.ofUser

FOR EACH ROW BEGIN

INSERT INTO paUserDetails (userID, firstname, lastname, street, zip, gender, nickname, image, city) VALUES (NEW.username, ‘’, ‘’, ‘’, ‘’, 0, ‘User’, ‘’, ‘’);

INSERT INTO paPushSettings (activated, user) VALUES (1, NEW.username);

END;

|

So, my next idea was to simply create multiple smaller Triggers…hwoever, only MySQL 5.7.2(+) supports this.

I am now stuck with manually adding triggers upon deployment of our plugins. This is really dangerous cause you can easily forget this.

I hope somebody can help me out with this one.

Thanks in advance.

I really hope somebody can help me out here.

Right now the only solution I see is to completely SKIP the default Database-Update System that is introduced by the Plugin Interface and instead handle all this Database Upgrading by myself in the Constructor of one of my plugins… I really do NOT want to do this though.

So, help please?