首页 > 数据库 >highgo数据库加触发器实例-记

highgo数据库加触发器实例-记

时间:2024-08-29 10:27:22浏览次数:12  
标签:building info 触发器 belong life 实例 key highgo areas

需求:t_key_areas_info发生变化时,更新t_building_info_manage表相关字段。

1、新建function

create or replace function t_key_areas_info_trigger_func()
returns trigger
as $$
declare
BEGIN
    IF( TG_OP = 'INSERT' ) THEN
        RAISE NOTICE 'this is a raise demo, INSERT ------------------------- ';
        RAISE NOTICE 'this is a raise demo, int_id is % ,new belong_building is % ',new.int_id,new.belong_building;
        UPDATE city_life_line.t_building_info_manage T 
            SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 ),
            T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ),
            T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ),
            T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ),
            T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND status = '1' ) 
        WHERE
            T.int_id = NEW.belong_building;        
    elsif ( TG_OP = 'UPDATE' ) THEN
        RAISE NOTICE 'this is a raise demo, UPDATE------------------- ';
        RAISE NOTICE 'this is a raise demo, int_id is % ,new belong_building is % ,old belong_building is %',new.int_id,new.belong_building,old.belong_building;    
        UPDATE city_life_line.t_building_info_manage T 
            SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 ),
            T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ),
            T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ),
            T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ),
            T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = NEW.belong_building AND stateflag = 0 AND status = '1' ) 
        WHERE
            T.int_id = NEW.belong_building;
        UPDATE city_life_line.t_building_info_manage T 
            SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 ),
            T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ),
            T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ),
            T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ),
            T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND status = '1' ) 
        WHERE
            T.int_id = OLD.belong_building;     
    elsif ( TG_OP = 'DELETE' ) THEN
        RAISE NOTICE 'this is a raise demo, DELETE------------------- ';
        RAISE NOTICE 'this is a raise demo, int_id is % ,belong_building is %',OLD.int_id,OLD.belong_building;
        UPDATE city_life_line.t_building_info_manage T 
            SET T.key_part_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 ),
            T.first_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '1' ),
            T.sec_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '2' ),
            T.third_resistance_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND fire_resistance_rating = '3' ),
            T.except_state_num = ( SELECT COUNT ( 1 ) FROM city_life_line.t_key_areas_info WHERE belong_building = OLD.belong_building AND stateflag = 0 AND status = '1' ) 
        WHERE
                T.int_id = OLD.belong_building;            
    END IF;
    RETURN NULL;        
END;
$$ LANGUAGE plpgsql;

2、新建触发器

CREATE TRIGGER trigger_t_key_areas_info after INSERT 
OR DELETE 
    OR UPDATE ON city_life_line.t_key_areas_info FOR EACH ROW
    EXECUTE PROCEDURE city_life_line.t_key_areas_info_trigger_func();

3、查询触发器

SELECT pg_get_triggerdef(oid)
FROM pg_trigger
WHERE tgname = 'trigger_t_key_areas_info';

4、删除触发器

DROP TRIGGER trigger_t_key_areas_info ON city_life_line.t_key_areas_info CASCADE; 

 

标签:building,info,触发器,belong,life,实例,key,highgo,areas
From: https://www.cnblogs.com/nuomm/p/18386084

相关文章

  • highgo数据库操作 瀚高
    publicstaticvoidmain(String[]args){try{Connectionconn;Stringname="com.highgo.jdbc.Driver";Stringurl="jdbc:highgo://120.224.9.106:5866/tas_qlk?currentSchema=schemaname";Stringuser=&quo......
  • CrossEntropy Loss 计算实例讲解
    CrossEntropyLoss整个计算过程前提条件:分类标签labels和模型输出结果outputs=model(inputs)outputs为一个未经过softmax的logits向量......
  • java实现线性反馈移位寄存器实例
    题目:3级线性反馈移位寄存器C3=1时可有4种线性反馈函数,设其初始状态为(a1,a2,a3)=(1,0,1),输出由它们得到的密钥流,并分别利用生成的密钥流对明文“0x0123456789ABCDEF”进行加密,输出加密后的结果,再对密文进行解密,输出解密后的结果。1.分析相关题目详解:3级线性反馈移位寄存器......
  • 网络安全实训六(靶机实例DC-3)
    1信息收集1.1获取靶机IP1.2扫描靶机网站的目录1.3扫描端口和服务器信息1.4进入网站1.5在msf中给搜索joomla扫描器1.6设置参数查看joomla版本信息1.7按照版本号搜索漏洞1.8查看漏洞使用2渗透2.1查看是否存在SQL注入2.2获取到数据库......
  • python实例演示贝叶斯定理在机器学习中的应用
    贝叶斯定理是一种概率论中的基本公式,用于计算在已知条件下事件发生的概率。它的通俗解释可以理解为:当你获得新信息时,如何更新对某个事件发生概率的判断。贝叶斯定理公式贝叶斯定理的数学表达式是:P(A∣B)=P(B∣A)⋅P(A)P(B)P(A|B)=\frac{P(B|A)\cdotP(A)}{P(B)}P(A∣B)=P......
  • C++学习随笔——简单的单例设计模式实例
    点击查看代码#include<iostream>classSingleton{private://私有化构造函数,防止外部实例化Singleton(){std::cout<<"SingletonInstanceCreated!"<<std::endl;}//删除拷贝构造函数和赋值运算符,防止拷贝实例Singleton(constSin......
  • SQLserver中的触发器和存储过程
    在SQLServer中,触发器是一种特殊的存储过程,它在指定的数据库表上发生特定的数据修改事件时自动执行。触发器可以用于执行各种任务,如数据验证、数据审计、自动更新相关表等。触发器的类型SQLServer支持以下几种类型的触发器:INSERT触发器:在向表中插入新行时触发。UPD......
  • SAM 2——视频和图像实时实例分割的全新开源模型
    引言源码地址:https://github.com/facebookresearch/segment-anything-2过去几年,人工智能领域在文本处理的基础人工智能方面取得了显著进步,这些进步改变了从客户服务到法律分析等各个行业。然而,在图像处理方面,我们才刚刚开始。视觉数据的复杂性以及训练模型以准确解释和分......
  • Android车载蓝牙音乐实例(附Demo源码):实现手机播放音乐后车机应用显示音乐名称,歌手,专辑
    一、功能需求功能需求是在Android10以上设备上实现蓝牙音乐功能,细分为两个功能点:1、手机和车载设备实现蓝牙连接(本Demo文只做监听蓝牙连接状态,需手动到设置中连接蓝牙)2、连接蓝牙成功后手机播放音乐时车载设备也能播放音乐,并且在车机应用上显示音乐名称,歌手,专辑名。可在车......
  • 使用kubectl创建多实例容器
    使用Kubernetes生成容器的多个实例可以通过以下步骤实现:创建一个Kubernetes集群:首先,需要在云平台上创建一个Kubernetes集群,例如腾讯云的容器服务TKE。在TKE上,可以通过控制台或API创建一个Kubernetes集群,选择合适的地域、可用区、节点配置等。编写容器镜像的描述文件:在生成容器......