postgresql(1)------创建索引触发器

postgresql(1)------ 创建索引  触发器

项目中用到触发器和触发器来插入4个固定字段  

库中许多表中有固定字段created_by，updated_by，date_created，date_updated需要插入时指定用户修改时间和创建时间，这时就需要触发器函数和触发器来协作。

我是按照下面2步来完成的:

1:创建触发器修改和插入：

CREATE TRIGGER "public.hw_property_bi"

        BEFORE INSERT

        ON public.hw_property

        FOR EACH ROW

        EXECUTE PROCEDURE public.tri_hw_usr_date_bi();

CREATE TRIGGER "public.hw_property_bu"

        BEFORE UPDATE

        ON public.hw_property

        FOR EACH ROW

        EXECUTE PROCEDURE public.tri_hw_usr_date_bu();

CREATE TRIGGER "public.hw_service_order_item_bi"

    BEFORE INSERT

    ON public.hw_property

    FOR EACH ROW

    EXECUTE PROCEDURE pubic.tri_siapp_usr_date_bi();

CREATE TRIGGER "public.hw_service_order_item_bu"

    BEFORE UPDATE

    ON public.hw_property

    FOR EACH ROW

    EXECUTE PROCEDURE public.tri_hw_usr_date_bu();

2:创建触发器函数

CREATE OR REPLACE FUNCTION public.tri_hw_usr_date_bi()

    RETURNS trigger AS

$BODY$

declare

    v_tri_usr := 'admin';

    v_tri_date := current_date;

   NEW.created_by := v_tri_usr;

   NEW.updated_by :=v_tri_usr;

   NEW.date_created := v_tri_date;

   NEW.date_updated := v_tri_date; 

return NEW;

end

$BODY$

        LANGUAGE plpgsql VOLATILE

        COST 100;

ALTER FUNCTION public.tri_hw_usr_date_bi()

       OWNER TO trdmalldata;

CREATE OR REPLACE FUNCTION public.tri_hw_usr_date_bu()

    RETURNS trigger AS

$BODY$

declare

    v_tri_usr character varying(100);

    v_tri_date date;

begin

v_tri_usr :='admin';

v_tri_date := current_date;

NEW.updated_by := v_tri_usr;

NEW.date_updated := v_tri_usr;

return NEW;

end

$BODY$

        LANGUAGE plpgsql VOLATILE

        COST 100;

ALTER FUNCTION public.tri_hw_usr_date_bu()

       OWNER TO trdmalldata;