I have a trigger applied on my database table. But when i insert data into it via hibernate jpa it creates duplicate rows in master table. Here is the trigger
CREATE OR REPLACE FUNCTION SMS_RECEIPT_func_insert_trigger()
RETURNS TRIGGER AS $$
BEGIN
IF ( NEW.timedate >= '2015-01-01' AND NEW.timedate < '2015-01-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m01 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-02-01' AND NEW.timedate < '2015-02-28' ) THEN
INSERT INTO SMS_RECEIPT_y2015m02 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-03-01' AND NEW.timedate < '2015-03-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m03 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-04-01' AND NEW.timedate < '2015-04-30' ) THEN
INSERT INTO SMS_RECEIPT_y2015m04 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-05-01' AND NEW.timedate < '2015-05-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m05 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-06-01' AND NEW.timedate < '2015-06-30' ) THEN
INSERT INTO SMS_RECEIPT_y2015m06 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-07-01' AND NEW.timedate < '2015-07-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m07 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-08-01' AND NEW.timedate < '2015-08-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m08 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-09-01' AND NEW.timedate < '2015-09-30' ) THEN
INSERT INTO SMS_RECEIPT_y2015m09 VALUES (NEW.*);
ELSIF ( NEW.timedate >= '2015-10-01' AND NEW.timedate < '2015-10-31' ) THEN
INSERT INTO SMS_RECEIPT_y2015m010 VALUES (NEW.*);
ELSE
RAISE EXCEPTION 'Date out of range. Fix the measurement_insert_trigger() function!';
END IF;
RETURN NULL;
END;
$$
LANGUAGE plpgsql;
CREATE TRIGGER trigger_SMS_RECEIPT_insert
AFTER INSERT ON "SMS_RECEIPT"
FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();
If use Before in place of AFTER it gives the following error in java
CREATE TRIGGER trigger_SMS_RECEIPT_insert
BEFORE INSERT ON "SMS_RECEIPT"
FOR EACH ROW EXECUTE PROCEDURE SMS_RECEIPT_func_insert_trigger();
Caused by: org.hibernate.StaleStateException: Batch update returned unexpected row count from update [0]; actual row count: 0; expected: 1
I also tried using INSTEAD OF inplace of AFTER and before but its giving
Tables cannot have INSTEAD OF triggers. I am new to table partitioning and using triggers. I have searched for all this in google and also gone through the post here: Insert trigger ends up inserting duplicate rows in partitioned table
But it didn't work for me.
Can anyone tell me how to resolve this.