Create Trigger to update the record that was newly inserted on PostgreSQL-CodePudding

I want the trigger to fire when a new record is inserted, and update that newly inserted record to add a current date and time, need help please be kind still new here :(

here's my code:

FUNCTION:

CREATE OR REPLACE FUNCTION dbo.func_update_date_sync()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
    BEGIN
        UPDATE dbo.rptowner SET date_sync = NOW()
        WHERE dbo.rptowner.owner_no = (SELECT MAX(dbo.rptowner.owner_no) FROM dbo.rptowner);
    RETURN NEW;
    END;
$BODY$
;

TRIGGER:

CREATE TRIGGER trg__update_date_sync 
AFTER INSERT
ON dbo.rptowner FOR EACH ROW EXECUTE PROCEDURE dbo.func_update_date_sync()

CodePudding user response：

If you only need a timestamp when a new record is created you can use NOW() as default value. There is no need to use a trigger.

ALTER TABLE dbo.rptowner ALTER COLUMN date_sync SET DEFAULT NOW();

CodePudding user response：

Your WHERE condition is needlessly complicated (and slow) as you could replace it with where owner_no = new.owner_no as you have full access to the inserted (or updated) row in the trigger.

However, there is no need for a costly UPDATE statement. Use a BEFORE trigger, and assign the values to the fields in the new record:

CREATE OR REPLACE FUNCTION dbo.func_update_date_sync()
RETURNS trigger
LANGUAGE plpgsql
AS $BODY$
BEGIN
  new.date_sync := now();
  RETURN NEW;
END;
$BODY$
;

And declare the trigger as a BEFORE trigger:

CREATE TRIGGER trg__update_date_sync 
  BEFORE INSERT ON dbo.rptowner 
  FOR EACH ROW EXECUTE PROCEDURE dbo.func_update_date_sync()

As a side note: Most people are not aware that now() is a synonym for transaction_timestamp(). So all rows inserted or updated in a single transaction will have exactly the same value for date_sync. If that is OK for you, then everything is fine. If you expect each row to have a (slightly) different timestamp, you should use clock_timestamp() instead.