I have a before insert trigger in MySQL that works when row is inserted directly (through DB client) into database but DOES NOT work through Hibernate ORM.
Some basic info
- MySQL 5.6.26 on Debian Jessie 8.6
- MySQL Connector Java 5.1.40
- Hibernate ORM 5.2.4
the Product table
+----------------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+----------------+---------------------+------+-----+---------+----------------+
| id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| prd_no | varchar(6) | YES | UNI | NULL | |
| prd_ty | varchar(20) | NO | | NULL | |
| prd_name | varchar(50) | NO | | NULL | |
| prd_name_short | varchar(10) | NO | | NULL | |
| prd_cat | varchar(20) | NO | | NULL | |
| prd_tax_ty | varchar(20) | NO | | NULL | |
| prd_price_ty | varchar(20) | NO | | NULL | |
| prd_norm_avail | tinyint(3) unsigned | NO | | 1 | |
| ppp_id | int(10) unsigned | YES | MUL | NULL | |
+----------------+---------------------+------+-----+---------+----------------+
My trigger
DELIMITER $$
CREATE TRIGGER tg_product_insPrdNo BEFORE INSERT ON product
FOR EACH ROW
BEGIN
IF NEW.prd_ty = 'ns' THEN
SET NEW.prd_no = fn_getPrdNoNextAI();
END IF;
END $$
DELIMITER ;
Function that gets called by the trigger
DELIMITER //
CREATE FUNCTION fn_getPrdNoNextAI()
RETURNS INTEGER SIGNED
BEGIN
DECLARE last_prd_no INTEGER SIGNED;
SELECT (MAX(CAST(product.prd_no AS SIGNED))) INTO last_prd_no FROM product;
IF last_prd_no IS NULL THEN
SET last_prd_no = 0;
END IF;
RETURN last_prd_no+1;
END //
DELIMITER ;
Java code (abbreviated) for inserting row
Session session = AES_Server.getSessionFactory().openSession();
Transaction tx = null;
int prdID = -1;
try {
tx = session.beginTransaction();
prdID = (int) session.save(prd);
tx.commit();
} catch (HibernateException he) {
if (tx != null)
tx.rollback();
} finally {
session.close();
}
return prdID;
Basically, my DB trigger will only auto-insert value for prd_no field if prd_ty is of value 'ns'. This works fine when inserting directly into the DB but when using hibernate to insert, no value is inserted.
Any pointers would be much appreciated. Cheers.