From: venkatramana@effigent.net
Subject: how to use triggers in hibernate
Date: 24 May 2006 6:09:26 PM GMT+05:30
To: hibernate-devel@lists.sourceforge.net

Hi ,

I have a requirement where i need to audit the insert/update and delete of records in the table , i am using triggers to do this (i know hibernate has interceptors for audit logging) .

I have created the trigger on the corresponding table and started the application ,but when i try to create or insert a record through application the trigger was not fired. when we looked into the database the trigger was droped from the database.
When i debuged the issue i found that when i started the application it loads the hibernate.cfg.xml file where it updates the database schema based on the hbm files (i have commented the hbm2ddl.auto property i think by default it is taking update.)

Following is my trigger(in Postgres) and tables.

CREATE TABLE test
(
lu_uom varchar(255) NOT NULL,
ty_uom varchar(255),
fl_uom_eng_mc bool,
nm_uom varchar(255),
);



CREATE TABLE test_audit
(
action char(1) NOT NULL,
modifiedtime timestamp NOT NULL,
userid text NOT NULL,
lu_uom varchar(255) NOT NULL,
ty_uom varchar(255),
fl_uom_eng_mc bool,
nm_uom varchar(255),
);


CREATE OR REPLACE FUNCTION process_test_audit() RETURNS TRIGGER AS '
BEGIN
--
-- Create a row in emp_audit to reflect the operation performed on emp,
-- make use of the special variable TG_OP to work out the operation.
--
IF (TG_OP =''DELETE'') THEN
INSERT INTO test_audit SELECT ''D'', now(), user, OLD.lu_uom, OLD.ty_uom, OLD.fl_uom_eng_mc, OLD.nm_uom;
RETURN OLD;
ELSIF (TG_OP = ''UPDATE'') THEN
INSERT INTO test_audit SELECT ''U'', now(), user, NEW.lu_uom,NEW.ty_uom,NEW.fl_uom_eng_mc,NEW.nm_uom;
RETURN NEW;
ELSIF (TG_OP = ''INSERT'') THEN
INSERT INTO test_audit SELECT ''I'', now(), user, NEW.lu_uom,NEW.ty_uom,NEW.fl_uom_eng_mc,NEW.nm_uom;
RETURN NEW;
END IF;
RETURN NULL; -- result is ignored since this is an AFTER trigger
END;
' LANGUAGE plpgsql;



CREATE TRIGGER test_audit
AFTER INSERT OR UPDATE OR DELETE ON test
FOR EACH ROW EXECUTE PROCEDURE process_test_audit();





Do i am missing anything here or do i need to mention any property in the hbm file.

Please help me out

Thanks in advance
Rgahavender

You believe Hibernate is redefining your database and removing triggers? That doesn't sound right. That sort of behaviour wouldn't make sense. I think you may be misinterpreting what is happening, or there is a pretty serious bug in Hibernate. If that were the intended behaviour, I would consider it to be unuseable.