CREATE trigger [TRG_MRQC_BRZT_INSERT]
on [MRQC_BRZT]
for insert
as
begin
declare @ztid numeric(12,0), @czlx numeric(18,0),@czsj datetime ,@czgh varchar(10),@glid varchar(10)
declare @kssj datetime ,@jssj datetime
declare @ErrorMessage VARCHAR(400),@ErrorSeverity INT,@ErrorState INT
declare @bzxx varchar(20)
select @kssj=kssj,@jssj=jssj from inserted
--1,一条记录新发生,开始时间不为空,结束时间为空
if @kssj is not null and @jssj is null
begin
if update(kssj)
set @bzxx = '新状态发生' --备注信息:状态发生
set @czlx=1 --操作类型
select @ztid=ztid,@czsj=kssj,@czgh =ksgh,@glid=glid from inserted
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(@ztid,@czlx,@czsj,@czgh,@glid,@bzxx)
end
--2,一条记录新结束,结束时间不为空,开始时间为空
if @kssj is null and @jssj is not null
begin
if update(jssj)
set @bzxx='新状态结束' --备注信息:以前未发生的状态结束
set @czlx=2 --操作类型
select @ztid=ztid,@czsj=jssj,@czgh =jsgh,@glid=glid from inserted
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(@ztid,@czlx,@czsj,@czgh,@glid,@bzxx)
end
end
on [MRQC_BRZT]
for insert
as
begin
declare @ztid numeric(12,0), @czlx numeric(18,0),@czsj datetime ,@czgh varchar(10),@glid varchar(10)
declare @kssj datetime ,@jssj datetime
declare @ErrorMessage VARCHAR(400),@ErrorSeverity INT,@ErrorState INT
declare @bzxx varchar(20)
select @kssj=kssj,@jssj=jssj from inserted
--1,一条记录新发生,开始时间不为空,结束时间为空
if @kssj is not null and @jssj is null
begin
if update(kssj)
set @bzxx = '新状态发生' --备注信息:状态发生
set @czlx=1 --操作类型
select @ztid=ztid,@czsj=kssj,@czgh =ksgh,@glid=glid from inserted
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(@ztid,@czlx,@czsj,@czgh,@glid,@bzxx)
end
--2,一条记录新结束,结束时间不为空,开始时间为空
if @kssj is null and @jssj is not null
begin
if update(jssj)
set @bzxx='新状态结束' --备注信息:以前未发生的状态结束
set @czlx=2 --操作类型
select @ztid=ztid,@czsj=jssj,@czgh =jsgh,@glid=glid from inserted
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(@ztid,@czlx,@czsj,@czgh,@glid,@bzxx)
end
end
/*
凭感觉改了下,未经测试
*/
create or replace trigger trg_mrqc_brzt_insert
before insert on mrqc_brzt
for each row
declare
kssj date;
jssj date;
begin
kssj:=:new.kssj;
jssj:=:new.jssj;
if kssj is not null and jssj is null then
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(:new.ztid,1,:new,kssj,:new.ksgh,:new.glid,'新状态发生');
end if;
if kssj is null and jssj is not null then
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(:new.ztid,2,:new,kssj,:new.ksgh,:new.glid,'新状态结束');
end if;
end;
BEFORE INSERT ON MRQC_BRZT
FOR EACH ROW
DECLARE
czlx NUMBER(18);
bzxx VARCHAR(20);
BEGIN
--1,一条记录新发生,开始时间不为空,结束时间为空
IF :new.kssj IS NOT NULL AND :new.jssj IS NULL THEN
IF updating(kssj) THEN
bzxx := '新状态发生'; --备注信息:状态发生
czlx := 1; --操作类型
INSERT INTO mrqc_ztrz
(ztid, czlx, czsj, czgh, glid, bzxx)
VALUES
(:new.ztid, czlx, :new.kssj, :new.ksgh, :new.glid, bzxx);
END IF;
END IF;
--2,一条记录新结束,结束时间不为空,开始时间为空
IF :new.kssj IS NULL AND :new.jssj IS NOT NULL THEN
IF updating(jssj) THEN
bzxx := '新状态结束'; --备注信息:以前未发生的状态结束
czlx := 2; --操作类型
INSERT INTO mrqc_ztrz
(ztid, czlx, czsj, czgh, glid, bzxx)
VALUES
(:new.ztid, czlx, :new.kssj, :new.ksgh, :new.glid, bzxx);
END IF;
END IF;
END;
/
for each row
declare
c_ztid number;
c_czlx number;
c_czsj date;
c_czgh varchar2(10);
c_glid varchar2(10);
c_kssj date;
c_jssj date;
c_ErrorMessage VARCHAR2(400);
c_ErrorSeverity INT;
ErrorState INT ;
bzxx varchar2(20);
begin
c_kssj:=:new.kssj;
c_jssj:=:new.jssj;
if c_kssj is not null and c_jssj is null then
c_bzxx:= '新状态发生'; --备注信息:状态发生
c_czlx:=1; --操作类型
c_ztid:=:new.ztid;
c_czsj:=:kssj;
c_czgh:=:ksgh;
c_glid:=:glid;
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(c_ztid,c_czlx,c_czsj,c_czgh,c_glid,c_bzxx);
end if;
if c_kssj is null and c_jssj is not null then
c_bzxx:='新状态结束'; --备注信息:以前未发生的状态结束
c_czlx:=2 ;--操作类型
c_ztid:=:new.ztid;
c_czsj:=:new.jssj;
c_czgh:=:new.jsgh;
c_glid:=:new.glid;
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glid,bzxx) values(c_ztid,c_czlx,c_czsj,c_czgh,c_glid,c_bzxx) ;
end if;
end;