PetaPoco insert fails on tables with multiple triggers in PostgreSQL

PetaPoco/Npgsql fails on insert with table trigger.

I believe this is very similar to PetaPoco insert fails on table with trigger, the difference being that question was for SQL 2008 and mine is for PostgreSQL 9.5.4 using the Npgsql C# driver with a table that has multiple triggers. My situation is that I have tables with multiple triggers defined in PostgreSQL like:

CREATE TABLE procedures
(
  recid serial NOT NULL,
  orderno integer NOT NULL,
  torder timestamp without time zone NOT NULL DEFAULT now(),
  cpt_recid integer NOT NULL,
  dx_recid integer,
  send_out boolean,
  modified timestamp without time zone NOT NULL DEFAULT now(),
)
WITH (
  OIDS=FALSE
);
ALTER TABLE procedures
  OWNER TO postgres;


CREATE TRIGGER update_modified
  BEFORE UPDATE
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

CREATE TRIGGER zzz_get_next_order_number_trigger
  BEFORE INSERT
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE getnextorderno();


CREATE OR REPLACE FUNCTION getnextorderno()
  RETURNS trigger AS
$BODY$

BEGIN
    NEW.orderno := nextval('order_number_seq');
    Return NEW; 
END;

$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION getnextorderno()
  OWNER TO postgres;

My primary problem is that the PetaPoco database.Insert() insists on saving 0 and epoch time instead of the next orderno or current time.

Is there a simple change that can be made to PetaPoco to respect either the DEFAULTS on the columns or allow the triggers to function correctly?

(I'm very new to PetaPoco).

TIA

Edit#1 Following the proposed changes, the table now looks like the below. The problem I'm still having is that orderno is still being filled in with 0 and hence violating the unique constraint.

duplicate key value violates unique constraint "procedure_order_unique" Key (orderno)=(0) already exists.

The PetaPoco insert is being called from the WCF service as:

p = new procedure
                    {
                        cpt_recid = cpt.recid,
                        chart_recid = _procedure.chart_recid,
                        dx_recid = _procedure.dx_recid,
                        torder = _procedure.torder,
                        on_return_to_office = _procedure.on_return_to_office,
                        send_out = _procedure.send_out,
                        standing_order = _procedure.standing_order,
                        stat = _procedure.stat,
                        tstop = _procedure.tstop,
                        isprocedure = _is_procedure
                    };
                    db.Insert(p);

CREATE TABLE procedures
(
  recid serial NOT NULL,
  orderno integer NOT NULL DEFAULT nextval('order_number_seq'::regclass),
  torder timestamp without time zone NOT NULL DEFAULT now(),
  cpt_recid integer NOT NULL,
  dx_recid integer,
  send_out boolean,
  modified timestamp without time zone NOT NULL DEFAULT now(),
  stat boolean,
  standing_order boolean,
  tstop timestamp without time zone,
  on_return_to_office boolean,
  chart_recid integer NOT NULL,
  isprocedure boolean NOT NULL DEFAULT false,
  is_deferred boolean,
  CONSTRAINT procedures_pk PRIMARY KEY (recid),
  CONSTRAINT procedures_chart_fk FOREIGN KEY (chart_recid)
      REFERENCES charts (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedures_cpt_fk FOREIGN KEY (cpt_recid)
      REFERENCES cpt (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedures_dx_fk FOREIGN KEY (dx_recid)
      REFERENCES dx (recid) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE RESTRICT,
  CONSTRAINT procedure_order_unique UNIQUE (orderno)
)
WITH (
  OIDS=FALSE
);
ALTER TABLE procedures
  OWNER TO postgres;

CREATE TRIGGER insert_modified
  BEFORE INSERT
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

CREATE TRIGGER update_modified
  BEFORE UPDATE
  ON procedures
  FOR EACH ROW
  EXECUTE PROCEDURE update_modified();

Thanks for any help.

Solution

You don't need the trigger for the order number, just set the sequence on the column and remove the need for the trigger.

The modified is being saved because your POCO modified property is not null so it's sending a default value. If you want to prevent this make your modified trigger run on both before insert / before update.

BEFORE INSERT OR UPDATE

http://www.toptensoftware.com/petapoco/

The documentation says to decorate the class definition with the primary key:

[PetaPoco.PrimaryKey("orderno")]

To get around decorating the table with attributes and modifying the T4 templates, you will need to name your column id instead.

PetaPoco will ignore this on insert and allow the database to handle it.

Any other column name will be assumed as non-primary key and the default value will be sent.

PostgreSQL and SQL Server will both ignore SERIAL and IDENTITY columns when the value is given on insert like what is happening in your scenario.