trig_loc.sql

公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统公安户口管理系统

------------当插入一条延期信息时,修改暂住人员信息中的信息--------------

drop trigger tr_addyqrq;
CREATE TRIGGER tr_addyqrq after insert On yqb000 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
  UPDATE zzxxjl  
     SET yqrq00 = new_dept.jsrq00,   --起始日期
         yqqx00 = new_dept.yxq000    --有效期
   WHERE zzxxjl.xh0000 = new_dept.zzxxzj   ;
END;

--------------修改暂住信息表中延期日期信息-------------------------

drop trigger tr_removeyqrq;
CREATE TRIGGER tr_removeyqrq before UPDATE On yqb000 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
  DECLARE li_rowcount integer ;
  DECLARE ls_jsrq00 char(8);
  DECLARE ls_yxq000 char(1);
  DECLARE ls_xh char(20);
  DECLARE ls_sql varchar(500);
  DECLARE ls_czwhxh char(12);
  
  if old_dept.jsrq00 <> new_dept.jsrq00 or old_dept.yxq000 <> new_dept.yxq000 then 
       UPDATE zzxxjl  
        SET yqrq00 = new_dept.jsrq00,   --起始日期
         yqqx00 = new_dept.yxq000    --有效期
       WHERE zzxxjl.xh0000 = new_dept.zzxxzj   ;   
  end if;
  
  if old_dept.zlbz00 = 'I' AND new_dept.zlbz00 = 'D' then 
   select count(*) into li_rowcount from yqb000 
   where zzxxzj = old_dept.zzxxzj;
   
   SELECT xh0000 into ls_czwhxh FROM sequer WHERE xhlx00 = 'zwhxh0'   ;
   update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zwhxh0'   ;  
    
   set ls_jsrq00 = '';
   set ls_yxq000 = '';
   
    if li_rowcount > 1 then --如果有多次延期,取出最后一次延期的数据
       select max(xh0000) into ls_xh from yqb000 where  zzxxzj = old_dept.zzxxzj
              and xh0000 <> old_dept.xh0000 and zlbz00 = 'I';
       select jsrq00,yxq000 into ls_jsrq00,ls_yxq000 from yqb000 where xh0000 = ls_xh;
	  UPDATE zzxxjl  
	     SET yqrq00 = ls_jsrq00,   --起始日期
        	 yqqx00 = ls_yxq000    --有效期
	   WHERE zzxxjl.xh0000 = old_dept.zzxxzj   ;
     else --如果只发生一次延期
	  UPDATE zzxxjl  
	     SET yqrq00 = '',   --起始日期
	         yqqx00 = ''    --有效期
	   WHERE zzxxjl.xh0000 = old_dept.zzxxzj   ;
     end if;
     
     set ls_sql = 'UPDATE zzxxjl SET yqrq00 = ' + CHAR(39) 
     + ls_jsrq00 + CHAR(39) + ' , yqqx00 = ' + CHAR(39) + ls_yxq000
     + CHAR(39) + ' WHERE xh0000 =  ' + CHAR(39) + old_dept.zzxxzj + CHAR(39)  ;
	
     INSERT INTO czwh00 (sql000,xh0000) VALUES (ls_sql,ls_czwhxh )  ;
   end if;
END;

-----------------删除暂住人员相关信息----------------


drop trigger tr_delete_xgxx;
CREATE TRIGGER tr_delete_xgxx before delete  On zzxxjl 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
   DELETE FROM xt0000 WHERE zzxxzj = OLD_DEPT.XH0000   ;
   DELETE FROM zp0000 WHERE zzzbh0 = OLD_DEPT.zzzbh0   ;

END;

-----------------删除携童信息,迁移信息，延期信息,注销信息----------------


drop trigger tr_delete_other;
CREATE TRIGGER tr_delete_other before UPDATE OF zlbz00  On zzxxjl 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN

  DECLARE ls_sql varchar(500);
  DECLARE ls_czwhxh char(12);

--删除携童
  update xt0000 set zlbz00 = 'D' WHERE zzxxzj = old_dept.xh0000   ;

   SELECT xh0000 into ls_czwhxh FROM sequer WHERE xhlx00 = 'zwhxh0'   ;
   update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zwhxh0'   ;  
   set ls_sql = 'update xt0000 set zlbz00 = ' + CHAR(39) + 'D' + CHAR(39) + ' WHERE zzxxzj = ' + CHAR(39) + old_dept.xh0000 + CHAR(39);
   INSERT INTO czwh00 (sql000,xh0000) VALUES (ls_sql,ls_czwhxh )  ;
     
--删除延期表
  update yqb000 set zlbz00 = 'D' WHERE zzxxzj = old_dept.xh0000   ;

   SELECT xh0000 into ls_czwhxh FROM sequer WHERE xhlx00 = 'zwhxh0'   ;
   update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zwhxh0'   ;  
   set ls_sql = 'update yqb000 set zlbz00 = ' + CHAR(39) + 'D' + CHAR(39) + ' WHERE zzxxzj = ' + CHAR(39) + old_dept.xh0000 + CHAR(39);
   INSERT INTO czwh00 (sql000,xh0000) VALUES (ls_sql,ls_czwhxh )  ;
 
--删除迁移表
  update qyb000 set zlbz00 = 'D' WHERE zzxxzj = old_dept.xh0000   ;

   SELECT xh0000 into ls_czwhxh FROM sequer WHERE xhlx00 = 'zwhxh0'   ;
   update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zwhxh0'   ;  
   set ls_sql = 'update qyb000 set zlbz00 = ' + CHAR(39) + 'D' + CHAR(39) + ' WHERE zzxxzj = ' + CHAR(39) + old_dept.xh0000 + CHAR(39);
   INSERT INTO czwh00 (sql000,xh0000) VALUES (ls_sql,ls_czwhxh )  ;

--违法信息表
   UPDATE FZDJB0 SET ZLBZ00 = 'D' WHERE ZZZBH0 = old_dept.zzzbh0   ;

   SELECT xh0000 into ls_czwhxh FROM sequer WHERE xhlx00 = 'zwhxh0'   ;
   update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zwhxh0'   ;  
   set ls_sql = 'update FZDJB0 set zlbz00 = ' + CHAR(39) + 'D' + CHAR(39) + ' WHERE ZZZBH0 = ' + CHAR(39) + old_dept.ZZZBH0 + CHAR(39);
   INSERT INTO czwh00 (sql000,xh0000) VALUES (ls_sql,ls_czwhxh )  ;
   
END;


-----------------在暂住信息表中插入标志------------------------

drop trigger tr_addzlbz;
CREATE TRIGGER tr_addzlbz after insert  On zzxxzx 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
  UPDATE zzxxjl SET zlbz00 = 'Z'  
   WHERE xh0000 = new_dept.xh0000   ;

END ;



-------------------产生照片维护信息---------------------------

drop trigger tr_zpwhxx;

CREATE TRIGGER tr_zpwhxx before UPDATE  On zp0000
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN

  DECLARE ls_xh char(12);

  SELECT xh0000 into ls_xh FROM sequer WHERE xhlx00 = 'zpwhxh'   ;
  update sequer set xh0000 = xh0000 + 1 where  xhlx00 = 'zpwhxh'   ;  
 
  INSERT INTO zpwh00 (zzzbh0,xh0000) VALUES ( old_dept.zzzbh0,ls_xh )  ;
END;


----------------当插入一条迁移信息时,修改暂住人员的暂住信息--------------

drop trigger tr_addqydz;
CREATE TRIGGER tr_addqydz after insert On qyb000 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN

  UPDATE  zzxxjl  
     SET zcdssx = new_dept.zcdssx,
         zcdxz0 = new_dept.zcdxz0,
         zcdpcs = new_dept.zcdpcs,
         zcdljx = new_dept.zcdljx,  
         zcdjwh = new_dept.zcdjwh,
         zcdmph = new_dept.zcdmph,
         zcdfh0 = new_dept.zcdfh0,
         zcdfjh = new_dept.zcdfjh
   WHERE xh0000 = new_dept.zzxxzj   ;

END;


----------------恢复暂住信息表中暂住地信息--------------------

drop trigger tr_removeqydz;
CREATE TRIGGER tr_removeqydz before UPDATE OF zlbz00  On qyb000 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
  UPDATE  zzxxjl  
     SET zcdssx = new_dept.ycdssx,
         zcdxz0 = new_dept.ycdxz0,
         zcdpcs = new_dept.ycdpcs,
         zcdljx = new_dept.ycdljx,  
         zcdjwh = new_dept.ycdjwh,
         zcdmph = new_dept.ycdmph,
         zcdfh0 = new_dept.ycdfh0,
         zcdfjh = new_dept.ycdfjh
   WHERE xh0000 = new_dept.zzxxzj   ;
END;


--------------修改房东信息表时修改相关信息-------------------------

drop trigger tr_markxkzczf;
CREATE TRIGGER tr_markxkzczf before UPDATE OF zlbz00  On houser 
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
   UPDATE czfxxb SET zlbz00 = 'D' WHERE fdzj00 = new_dept.xh0000 ;
   UPDATE xkz000 SET zlbz00 = 'D' WHERE fdzj00 = new_dept.xh0000 ;
end;

--------------修改房东信息表时修改相关信息-------------------------

drop trigger tr_markczf;
CREATE TRIGGER tr_markczf before UPDATE OF zlbz00  On xkz000
REFERENCING OLD AS old_dept NEW AS new_dept FOR EACH ROW
BEGIN
   UPDATE czfxxb SET zlbz00 = 'D' WHERE XKZBH0 = new_dept.CZFBH0 ;
end;