17,086
社区成员
发帖
与我相关
我的任务
分享
create or replace trigger tri_add before insert on table1 for each row
declare
num number;
begin
select count(*) into num from table3 where stid=:new.stid;
if num>0 and(:new.rain>rain_a or :new.water>water_b) then
insert into table2 select id,:new.rain-rain_a,:new.water-water_b,stid from table3 where stid=:new.stid;
end if;
end;
--"+"号都改成“||”
CREATE OR REPLACE TRIGGER callthepolice_trg
AFTER INSERT ON data
FOR EACH ROW
DECLARE
v_rain NUMBER;
v_water NUMBER;
v_voltagehigh NUMBER;
v_voltagelow NUMBER;
BEGIN
SELECT rain,water,voltagehigh,voltagelow INTO v_rain,v_water,v_voltagehigh,v_voltagelow FROM callthepolice WHERE stid=:NEW.stid;
IF :NEW.dayrainfall>v_rain THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.dayrainfall,1,:NEW.surveytime,'雨量超过警戒值'||(:NEW.dayrainfall-v_rain));
END IF;
IF :NEW.waterlevel>v_water THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.waterlevel,1,:NEW.surveytime,'水位超过警戒值'||(:NEW.waterlevel-v_water));
END IF;
IF :NEW.voltage>v_voltagehigh THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.voltage,1,:NEW.surveytime,'电压超过警戒值'||(:NEW.voltage-v_voltagehigh));
END IF;
IF :NEW.voltage<v_voltagelow THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.voltage,1,:NEW.surveytime,'电压低于警戒值'||(v_voltagelow-:NEW.voltage));
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
insert into data(id,STID,SurveyTime,WaterLevel,SumRainfall,DayRainfall,Voltage,dtype,rectime,State) values(AAA.nextVal,'55',to_date('2010-11-11 11:11:12','yyyy-mm-dd hh24:mi:ss'),0,0,11,0,'人工数据',to_date('2010-11-11 11:11:12','yyyy-mm-dd hh24:mi:ss'),0)
1、首先确认你这条查询能找到数据:
SELECT rain,water,voltagehigh,voltagelow INTO v_rain,v_water,v_voltagehigh,v_voltagelow FROM callthepolice WHERE stid=:NEW.stid;
2、插入的数据rain,water,voltagehigh,voltagelow是否满足你触发器中if判断条件
3、往data表插入数据后,要提交 COMMIT;
CREATE OR REPLACE TRIGGER callthepolice_trg
AFTER INSERT ON data
FOR EACH ROW
DECLARE
v_rain NUMBER;
v_water NUMBER;
v_voltagehigh NUMBER;
v_voltagelow NUMBER;
BEGIN
SELECT rain,water,voltagehigh,voltagelow INTO v_rain,v_water,v_voltagehigh,v_voltagelow FROM callthepolice WHERE stid=:NEW.stid;
IF :NEW.dayrainfall>v_rain THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.dayrainfall,1,:NEW.surveytime,'雨量超过警戒值'+:NEW.dayrainfall-v_rain);
END IF;
IF :NEW.waterlevel>v_water THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.waterlevel,1,:NEW.surveytime,'水位超过警戒值'+:NEW.waterlevel-v_water);
END IF;
IF :NEW.voltage>v_voltagehigh THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.voltage,1,:NEW.surveytime,'电压超过警戒值'+:NEW.voltage-v_voltagehigh);
END IF;
IF :NEW.voltage<v_voltagelow THEN
INSERT INTO t_callthepolice(inspectnum,measure,state,time,text) VALUES(:NEW.stid,:NEW.voltage,1,:NEW.surveytime,'电压低于警戒值'+v_voltagelow-:NEW.voltage);
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
create table T_CALLTHEPOLICE
(
CTPID VARCHAR2(32 CHAR) not null,
INSPECTNUM VARCHAR2(30 CHAR),
MEASURE VARCHAR2(30 CHAR),
STATE VARCHAR2(10 CHAR),
TIME DATE,
TEXT VARCHAR2(500 CHAR),
DISPOSE VARCHAR2(100 CHAR),
DISPOSERESULT VARCHAR2(1000 CHAR),
STNM VARCHAR2(30 CHAR)
)
create table CALLTHEPOLICE
(
STID NUMBER(10),
RAIN NUMBER(10),
WATER NUMBER(10,5),
VOLTAGEHIGH NUMBER(10,5),
VOLTAGELOW NUMBER(10,5)
)
create table DATA
(
ID NUMBER(10) not null,
STID NUMBER(10),
SURVEYTIME DATE,
RECTIME DATE,
DTYPE VARCHAR2(50 CHAR),
HOURRAINFALLL NUMBER(19,9),
DAYRAINFALL NUMBER(19,9),
SUMRAINFALL NUMBER(19,9),
WATERHEAD NUMBER(19,9),
WATERLEVEL NUMBER(19,9),
VOLTAGE NUMBER(19,9),
CAPACITY NUMBER(19,9),
FLOW NUMBER(19,9),
SENID NUMBER(10),
TEMP NUMBER(19,9),
FREQUENCY NUMBER(19,9),
STATE NUMBER(5),
BREAD NUMBER(1),
SPATH VARCHAR2(50 CHAR)
)
CREATE OR REPLACE TRIGGER trig
BEFORE INSERT ON table1
FOR EACH ROW
DECLARE
v_rain_a NUMBER;
v_water_b NUMBER;
BEGIN
BEGIN
SELECT rain_a,water_b INTO v_rain_a,v_water_b FROM table3 WHERE stid=:NEW.stid;
IF :NEW.rain>v_rain_a OR :NEW.water>v_water_b THEN
INSERT INTO table2(id,a,b) VALUES(:NEW.id,:NEW.rain-v_rain_a,:NEW.water-v_water_b);
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END;
--上面那个欠考虑,找不到数据时,不应该将变量v_rain_a和v_water_b设置为0,,然后以0为基础作判断插值
--修改后如下:
CREATE OR REPLACE TRIGGER trig
BEFORE INSERT ON table1
FOR EACH ROW
DECLARE
v_rain_a NUMBER;
v_water_b NUMBER;
BEGIN
BEGIN
SELECT rain_a,water_b INTO v_rain_a,v_water_b FROM table3 WHERE stid=:NEW.stid;
IF :NEW.rain>v_rain_a OR :NEW.water>v_water_b THEN
INSERT INTO table2(id,a,b) VALUES(:NEW.id,:NEW.rain-v_rain_a,:NEW.water-v_water_b);
END IF;
EXCEPTION WHEN OTHERS THEN
NULL;
END;
END;
CREATE OR REPLACE TRIGGER trig
BEFORE INSERT ON table1
FOR EACH ROW
DECLARE
v_rain_a NUMBER;
v_water_b NUMBER;
BEGIN
BEGIN
SELECT rain_a,water_b INTO v_rain_a,v_water_b FROM table3 WHERE stid=:NEW.stid;
EXCEPTION WHEN OTHERS THEN
v_rain_a :=0;
v_water_b :=0;
END;
IF :NEW.rain>v_rain_a OR :NEW.water>v_water_b THEN
INSERT INTO table2(id,a,b) VALUES(:NEW.id,:NEW.rain-v_rain_a,:NEW.water-v_water_b);
END IF;
END;
CREATE OR REPLACE TRIGGER trig
BEFORE INSERT ON table1
FOR EACH ROW
WHEN(NEW.rain>1 OR NEW.water > 3)
BEGIN
INSERT INTO table2(id,a,b) VALUES(:NEW.id,:NEW.rain-1,:NEW.water-3);
END;