Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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.

share|improve this question
    
Triggers are evil. Move the logic to another place. For example, create a procedure that the client can call instead of doing an insert. –  Andomar May 28 at 12:10
1  
For a start it would need to be a before trigger. –  Colin 't Hart May 28 at 12:12
1  
@Andomar triggers are not necessarily evil, one of the established use cases in PostgreSQL is exactly partitioning. –  dezso May 28 at 15:51
    
Could you tell if PostgreSQL itself complains when inserting a row? –  dezso May 28 at 15:52
    
No it doesnt complain it inserts duplicate rows in master table with duplicate primary key. @dezso –  kirti May 28 at 16:37

1 Answer 1

You can (pick one)

  1. 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).
  2. 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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.