即使我将django模型的值设置为save，postgresql触发器也会执行吗？

Question

我已经在每一行上创建了一个触发器-- INSERT/UPDATE来分别设置created_at和updated_at。

触发器：

CREATE OR REPLACE FUNCTION insert_update_function() RETURNS trigger AS $BODY$
BEGIN
  -- CHECKING OPERATION TYPE AND DECIDE COLUMN
  IF TG_OP = 'INSERT' THEN
    NEW.created_at := current_timestamp;
  ELSE
    NEW.updated_at := current_timestamp;
  END IF;
  RETURN NEW;
END; $BODY$ LANGUAGE plpgsql VOLATILE;

仅应用updated_at列存在的表()

DO $$
DECLARE
    t text;
BEGIN
    FOR t IN
        SELECT table_name FROM information_schema.columns WHERE column_name = 'updated_at'
    LOOP
        EXECUTE format('CREATE TRIGGER insert_update_trigger
                    BEFORE INSERT OR UPDATE ON %I
                    FOR EACH ROW EXECUTE PROCEDURE insert_update_function()', t,t);
    END loop;
END;
$$ language plpgsql;

因此，当我确实从原始SQL插入/更新时，我没有提供这两列，即created_at和updated_at，它会自动触发并添加到我的表上，这是预期的，很好的，但是现在，当我试图通过设置created_at和updated_at的Django BaseModel插入/更新表的行时，触发器将再次被调用？。

带有自定义保存()的Django BaseModel

class TimeStampedModel(models.Model):
    """
    An abstract base class model that provides self-updating
    ``created_at`` and ``modified_at`` fields.
    """
    created_at = AutoCreatedField('created_at')
    updated_at = AutoLastModifiedField('updated_at')

    def save(self, *args, **kwargs):
        """
        Overriding the save method in order to make sure that
        updated_at field is updated even if it is not given as
        a parameter to the update field argument.
        """
        update_fields = kwargs.get('update_fields', None)
        if update_fields:
            kwargs['update_fields'] = set(update_fields).union({'updated_at'})

        super().save(*args, **kwargs)

    class Meta:
        abstract = True

Answer 1

是的，我们会叫它。我建议您为created_at字段创建默认值，如果django不提供该字段的值，该字段将设置值current_timestamp。

这是一个示例代码；

CREATE OR REPLACE FUNCTION public.insert_update_function() 
RETURNS trigger AS 
$BODY$
BEGIN
  -- CHECKING OPERATION TYPE AND DECIDE COLUMN
  IF TG_OP = 'INSERT' THEN
    -- if created_at comes null from application code then set current timestamp
    IF NEW.created_at IS NULL THEN
      NEW.created_at = current_timestamp;
    END IF;
  ELSE
    -- if updated_at is not provided in application code its value 
    --    will be equal to its old value even its `null`
    --    then we set current timestamp
    --    else nothing to do 
    IF OLD.updated_at IS NOT DISTINCT FROM NEW.updated_at THEN
      NEW.updated_at = current_timestamp;
    END IF;
  END IF;
  RETURN NEW;
END; 
$BODY$ 
LANGUAGE plpgsql 
VOLATILE;



DO LANGUAGE plpgsql
$$
DECLARE
  r record;
BEGIN
  -- set default value for all tables which has created_at column
  FOR r IN
    SELECT table_schema s, table_name t FROM information_schema.columns WHERE column_name = 'created_at'
  LOOP
    -- set default value
    EXECUTE format('ALTER TABLE %I.%I ALTER created_at SET DEFAULT CURRENT_TIMESTAMP', r.s, r.t);
    
    -- fill empty created_at values with current timestamp
    EXECUTE format('UPDATE %I.%I SET created_at = CURRENT_TIMESTAMP WHERE created_at IS NULL', r.s, r.t);
    
    -- created_at cannot be not null anymore
    EXECUTE format('ALTER TABLE %I.%I ALTER created_at SET NOT NULL', r.s, r.t);
    
  END loop;

  -- add update trigger for updated_at field.
  FOR r IN
    SELECT table_schema s, table_name t FROM information_schema.columns WHERE column_name = 'updated_at'
  LOOP
    EXECUTE format('CREATE TRIGGER %I BEFORE INSERT OR UPDATE ON %I.%I FOR EACH ROW EXECUTE PROCEDURE public.insert_update_function()', 
      r.t || '_insert_update_trigger',r.s,r.t,r.t);
  END loop;
END;
$$;