Postgres Partition Table Trigger inserting duplicate records

Question

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.

Could you tell if PostgreSQL itself complains when inserting a row? — András Váczi
– András Váczi, Commented May 28, 2015 at 15:52
@dezso no it doesnot give problem in postgres but it gives in java when i make it before.it gives 0 rows affected and that creates the problem — kirti
– kirti, Commented May 29, 2015 at 9:53

user_0 · Accepted Answer · 2015-05-28 12:06:42Z

1

You can (pick one)

Create a view and build on it the trigger with INSTEAD OF. Then you mae insert on the view, not on the table (you can have a trigger on the table raising an error if you want to be sure).
Do not make direct insert. Call a stored procedure and move logic there

More on trigger and where they can be applied here: http://www.postgresql.org/docs/9.3/static/sql-createtrigger.html

answered May 28, 2015 at 12:06

user_0

1,0331 gold badge10 silver badges26 bronze badges

Add a comment |

Stack Exchange Network

Postgres Partition Table Trigger inserting duplicate records

1 Answer 1

Hot Network Questions

Postgres Partition Table Trigger inserting duplicate records

1 Answer 1

Related

Hot Network Questions