powered by Jive Software

MySql Trigger within plugin

Hi,

I wrote small plugin for openfire and added also a sql-file which builds up my tables.

I want also some triggers to be inserted in the db, but whenever I put my plugin into the openfire server I get following error-log:

com.mysql.jdbc.exceptions.MySQLSyntaxErrorException: 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 'DELIMITER // DROP

TRIGGER IF EXISTS new_chat_message// CREATE TRIGGER new_chat’ at line

1

Obviously, there is a problem with the delimiter but I don’t know how to add a trigger without a special delimiter.

My SQL-File:

DELIMITER //

DROP TRIGGER IF EXISTS new_chat_message//

CREATE TRIGGER new_chat_message AFTER INSERT ON yeevaArchive

FOR EACH ROW BEGIN

IF (NEW.received=‘0’) THEN

INSERT INTO yeevaMsgTracker (id,from,to,time)

VALUES (NEW.id,NEW.from,NEW.to,NEW.time)

ON DUPLICATE KEY UPDATE id=NEW.id,time=NEW.time,

num=num+1;

INSERT INTO yeevaMsgCounter (user_id,buddy_id,send_num)

VALUES (NEW.from,NEW.to,1)

ON DUPLICATE KEY UPDATE send_num=send_num+1;

INSERT INTO yeevaMsgCounter (user_id,buddy_id,received_num)

VALUES (NEW.to,NEW.to,1)

ON DUPLICATE KEY UPDATE received_num=received_num+1;

END IF;

END;

//

DELIMITER ;

I also tried to load the mysql-file directly with the mysql-command and there was no problem.

Some ideas what are the reason for this or maybe a solution?

Thanks in advance,

Olli

I am currently runnin into the same problem.

The DELIMITER statement is not accepted. Any alternatives I could use?