trig_ora.sql

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

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

CREATE OR REPLACE TRIGGER tr_delete_xtxx AFTER delete  On zzxxjl FOR EACH ROW
BEGIN
   DELETE FROM xt0000 WHERE zzxxzj = :NEW.XH0000 ;
   DELETE FROM zp0000 WHERE zzzbh0 = :NEW.zzzbh0   ;
END ;
/

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

CREATE OR REPLACE TRIGGER tr_addyqrq after insert On yqb000 FOR EACH ROW
 BEGIN
   UPDATE zzxxjl
      SET yqrq00 = :new.jsrq00,   --起始日期
          yqqx00 = :new.yxq000    --有效期
    WHERE zzxxjl.xh0000 = :new.zzxxzj   ;
end ;
/

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

CREATE OR REPLACE TRIGGER tr_edityqrq after update On yqb000 FOR EACH ROW
 BEGIN
  if :old.jsrq00 <> :new.jsrq00 or :old.yxq000 <> :new.yxq000 then 
       UPDATE zzxxjl  
        SET yqrq00 = :new.jsrq00,   --起始日期
         yqqx00 = :new.yxq000    --有效期
       WHERE xh0000 = :new.zzxxzj   ;   
  end if;
end ;
/

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

CREATE OR REPLACE TRIGGER tr_addzlbz after insert  On zzxxzx 
 FOR EACH ROW
BEGIN
  UPDATE zzxxjl SET zlbz00 = 'Z'  
   WHERE xh0000 = :new.xh0000   ;

END ;
/

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

CREATE OR REPLACE TRIGGER tr_addqydz after insert On qyb000 
 FOR EACH ROW
BEGIN

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

END ;
/

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

CREATE OR REPLACE TRIGGER tr_removeqydz before UPDATE OF zlbz00  On qyb000 
 FOR EACH ROW
BEGIN
  UPDATE  zzxxjl  
     SET zcdssx = :new.ycdssx,
         zcdxz0 = :new.ycdxz0,
         zcdpcs = :new.ycdpcs,
         zcdljx = :new.ycdljx,  
         zcdjwh = :new.ycdjwh,
         zcdmph = :new.ycdmph,
         zcdfh0 = :new.ycdfh0,
         zcdfjh = :new.ycdfjh
   WHERE xh0000 = :new.zzxxzj   ;
END ;
/

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

CREATE OR REPLACE TRIGGER tr_markxkzczf before UPDATE OF zlbz00  On houser 
FOR EACH ROW
BEGIN
   UPDATE czfxxb SET zlbz00 = 'D' WHERE fdzj00 = :new.xh0000 ;
   UPDATE xkz000 SET zlbz00 = 'D' WHERE fdzj00 = :new.xh0000 ;
end ;
/
--------------修改房东信息表时修改相关信息-------------------------

CREATE OR REPLACE TRIGGER tr_markczf before UPDATE OF zlbz00  On xkz000
FOR EACH ROW
BEGIN
   UPDATE czfxxb SET zlbz00 = 'D' WHERE XKZBH0 = :new.CZFBH0 ;
end  ; 
/
-----------------删除携童信息,迁移信息，延期信息,注销信息----------------


CREATE OR REPLACE TRIGGER tr_delete_other before UPDATE OF zlbz00  On zzxxjl 
FOR EACH ROW
BEGIN

--删除携童
  update xt0000 set zlbz00 = 'D' WHERE zzxxzj = :old.xh0000   ;
     
--删除延期表
  update yqb000 set zlbz00 = 'D' WHERE zzxxzj = :old.xh0000   ;
 
--删除迁移表
  update qyb000 set zlbz00 = 'D' WHERE zzxxzj = :old.xh0000   ;

--违法信息表
   UPDATE FZDJB0 SET ZLBZ00 = 'D' WHERE ZZZBH0 = :old.zzzbh0   ;
   
END ;
/