触发器的使用

位置：相关数据库管理系统

关键字：TRIGGER

例子：

CREATE TRIGGER 触发器名称
  AFTER INSERT
  ON 触发触发器的表
  FOR EACH ROW

  EXECUTE PROCEDURE 触发器函数();

触发器函数：

CREATE OR REPLACE FUNCTION 触发器函数()
  RETURNS trigger AS
$BODY$
DECLARE
    声明变量（触发触发器的表中没有的字段）
BEGIN
    --new代表着一个新的数据，即，触发触发器的表中没有的字段
    SELECT INTO record_count count(*) FROM sys_stock WHERE location_cd = new.location_cd AND figure_num = new.figure_num AND edition = new.edition --and product_num = new.product_num;
           AND lot_number = new.lot_number AND x_dimension = new.x_dimension AND y_dimension = new.y_dimension AND z_dimension = new.z_dimension;


   
    changed_new_ukeharai_unit := new.unit * sys_find_ukeharai_kbn_func(new.ukeharai_class);
    
    changed_new_ukeharai_num := new.quantity * sys_find_ukeharai_kbn_func(new.ukeharai_class);
    
    if record_count > 0 THEN
--一条sql语句，查询多个字段，以逗号分隔，赋值使用空格隔开
        SELECT INTO  old_stock_num, old_unit stock_num, unit FROM sys_stock WHERE location_cd = new.location_cd AND figure_num = new.figure_num AND edition = new.edition --and product_num = new.product_num;
                    AND lot_number = new.lot_number AND x_dimension = new.x_dimension AND y_dimension = new.y_dimension AND z_dimension = new.z_dimension;



        cal_result_stock_num := coalesce(old_stock_num, 0) + coalesce(changed_new_ukeharai_num, 0);
        cal_result_unit := coalesce(old_unit, 0) + coalesce(changed_new_ukeharai_unit, 0);


        if cal_result_stock_num = 0 AND cal_result_unit = 0 THEN

            DELETE FROM sys_stock WHERE location_cd = new.location_cd AND figure_num = new.figure_num AND edition = new.edition --and product_num = new.product_num;
                      AND lot_number = new.lot_number AND x_dimension = new.x_dimension AND y_dimension = new.y_dimension AND z_dimension = new.z_dimension;
        else

            UPDATE sys_stock SET stock_num = (coalesce(old_stock_num, 0) + coalesce(changed_new_ukeharai_num, 0)), unit = (coalesce(old_unit, 0) + coalesce(changed_new_ukeharai_unit, 0))
            WHERE location_cd = new.location_cd AND figure_num = new.figure_num AND edition = new.edition --and product_num = new.product_num;
            AND lot_number = new.lot_number AND x_dimension = new.x_dimension AND y_dimension = new.y_dimension AND z_dimension = new.z_dimension;
        END if;
    else

        new_uuid = uuid_generate_v4();
        new_modifier = 'sys-person_sys_admin';
        new_creator = 'sys-person_sys_admin';
        INSERT INTO sys_stock(id,location_cd, figure_num, edition, product_num, stock_num,lot_number,x_dimension,y_dimension,z_dimension,unit,item_name,location_select,arrival_day,last_processed_date,supplier_cd,supplier_select,million_single_weight
) values(new_uuid,new.location_cd, new.figure_num, new.edition, NULL, new.quantity, new.lot_number, new.x_dimension, new.y_dimension, new.z_dimension, new.unit, new.item_name, new.location_select, NULL, NULL, NULL, NULL, NULL);
        INSERT INTO basedataset(id,rev_ctrl_id,name,created,modified,deleted,remarks,modifier,creator,owner,owned_org,thread_id,parent_id,layout_id,app_id,revision,abbr) VALUES(new_uuid,NULL,NULL,now(),now(),false,NULL,new_modifier,new_creator,NULL,NULL,NULL,NULL,'sys_stock','app_production_mng',0,NULL);
    END if;
    
    return new;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;