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
解决方案 »
- oracle7.33提示SGA错误,无法连接数据库!
- Oracle数据库 求一SQL 求百分比排名
- 高手,求解
- 环境变量
- orcale中如何判断一张数据表已经存在
- 求一个求和(sum)的SQL语句
- 我的简单问题:怎样建库?
- 救命啊 为什么在oracle的表编辑器里能执行的update语句 在servlet里执行后老出现Exception:java.lang.NullPointerException
- 安装Oracle9i时候后运行Database Configuration Assistant创建数据库的时候出现问题。
- Oracle8i有没有一个类似SQL Server Enterprise Manager那样的图形化的数据库管理工具?
- oracle中表之间的纠结
- 请问一个EM奇怪的问题·!
/*
凭感觉改了下,未经测试
*/
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;
既然是 for insert 触发器,还需要检查 update(kssj) ?
inserted 表返回的是行集。这样只能处理一次插入一行的操作,如果一次插入多行,使用这个触发器就有问题。
create or replace trigger trg_mrqc_brzt_insert
after insert on mrqc_brzt
for each row
begin
if :new.kssj is not null and :new.jssj is null then
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glib,bzxx)
values(:new.ztid,1,:new.kssj,:new.ksgh,:new.glib,'新状态发生');
end if;
if :new.kssj is null and :new.jssj is not null then
insert into mrqc_ztrz(ztid,czlx,czsj,czgh,glib,bzxx)
values(:new.ztid,2,:new.ksgh,:new.glib,'新状态结束');
end if;
end;
/
CREATE or replace trigger TRG_MRQC_BRZT_INSERT
instead of insert or update on MRQC_BRZT
for each row as
declare
ztid number(12, 0);
czlx number(18, 0);
czsj date;
czgh varchar2(10);
glid varchar2(10);
kssj date;
jssj date;
ErrorMessage VARCHAR2(400);
ErrorSeverity number;
ErrorState number;
bzxx varchar2(20);
begin
--1,一条记录新发生,开始时间不为空,结束时间为空
if :new.kssj is not null and :new.jssj is null then
if inserting then
:new.bzxx := '新状态发生'; --备注信息:状态发生
:new.czlx := 1; --操作类型
insert into mrqc_ztrz
(ztid, czlx, czsj, czgh, glid, bzxx)
values
(:new.ztid, :new.czlx, :new.czsj, :new.czgh, :new.glid, :new.bzxx);
end if;
end if;
--2,一条记录新结束,结束时间不为空,开始时间为空
if :new.kssj is null and :new.jssj is not null then
if inserting then
:new.bzxx = '新状态结束' --备注信息:以前未发生的状态结束
:new.czlx = 2 --操作类型
insert
into mrqc_ztrz(ztid, czlx, czsj, czgh, glid, bzxx) values(:new.ztid,
:new.czlx,
:new.czsj,
:new.czgh,
:new.glid,
:new.bzxx);
end if;
end if;
end;
after insert
on MRQC_BRZT
referencing new as new old as old
for each row
begin if :new.kssj is not null and :new.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 :new.kssj is null and :new.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;