java - 数据库触发器在行插入和更新时填充当前日期不起作用

Question

函数-

创建函数:

-- Function: created_func()

CREATE OR REPLACE FUNCTION created_func()
  RETURNS trigger AS
$BODY$
    BEGIN
        -- Remember who changed the payroll when
        NEW.created_ts := current_timestamp;
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION created_func()
  OWNER TO postgres;

更新函数:

-- Function: updated_func()

CREATE OR REPLACE FUNCTION updated_func()
  RETURNS trigger AS
$BODY$
    BEGIN
        -- Remember who changed the payroll when
        NEW.updated_ts := current_timestamp;
        RETURN NEW;
    END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION updated_func()
  OWNER TO postgres;

触发器-

创建触发器:

-- Trigger: created_func on drcschema.dr_event

CREATE TRIGGER created_func
  BEFORE INSERT
  ON drcschema.dr_event
  FOR EACH ROW
  EXECUTE PROCEDURE created_func();

更新触发器:

-- Trigger: update_func on drcschema.dr_event

CREATE TRIGGER update_func
  AFTER UPDATE
  ON drcschema.dr_event
  FOR EACH ROW
  EXECUTE PROCEDURE updated_func();

列-

created_ts:

-- 列:created_ts

ALTER TABLE drcschema.dr_event ADD COLUMN created_ts timestamp without time zone;

updated_ts:

-- 列:updated_ts

ALTER TABLE drcschema.dr_event ADD COLUMN updated_ts timestamp without time zone;

DrEvent.hbm.xml:

<property name="createdTs" type="timestamp" generated="insert">
   <column name="created_ts" length="29" />
</property>
<property name="updatedTs" type="timestamp" generated="always">
   <column name="updated_ts" length="29" />
</property>

在插入和更新行后，我仍然没有在 updated_ts 列中获取日期，如下所示-

Answer 1

您需要一个 BEFORE UPDATE 触发器，否则更改将不会保留到表中。

除此之外，对于如此相似的触发器功能，最好将它们组合成一个，以便“业务逻辑”位于一个地方，从而更易于维护:

CREATE OR REPLACE FUNCTION ins_upd_func() RETURNS trigger AS $BODY$
BEGIN
  -- Remember who changed the payroll when
  IF TG_OP = 'INSERT' THEN
    NEW.created_ts := current_timestamp;
  ELSE
    NEW.updated_ts := current_timestamp;
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;

触发器:

CREATE TRIGGER ins_upd_tr
BEFORE INSERT OR UPDATE ON drcschema.dr_event
FOR EACH ROW EXECUTE PROCEDURE ins_upd_func();