MySQL before insert trigger not invoked when object saved by hibernate orm

607 views Asked by At

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.

0

There are 0 answers