现在又2张表, table1 字段 id(number(10)), rain(number(10)), water(number(10)) table2 字段 id(number(10)), a(number(10)), b(varchar2(10))当每次往table1中插入数据时,如果 rain > 1 或者 water > 3 就往table2中插入一条数据 a= rain-1, b=water-3请教符合这种条件的触发器该如何编写,求代码。
小弟以前从来没有使用过触发器, 但现在项目里需要用到,请各位大侠不吝赐教。
小弟以前从来没有使用过触发器, 但现在项目里需要用到,请各位大侠不吝赐教。
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;
when(new.rain>1 or new.water>3)
begin
insert into tb2 values(:new.id,:new.rian-1,:new.water-3);
end;
如果rain>rain_a 或者 water>water_b
就往table2中插入一条数据。 a=rain-rain_a, b=water-water_b
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;
--上面那个欠考虑,找不到数据时,不应该将变量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; 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;我想将这里的BEFORE 改为AFTER,因为我想先让table1中的数据存入数据库再执行其他的,可以吗?
这里有2个BEGIN 和 END,是否可以删除已对?
WHERE stid=:NEW.stid; 这个语句中的 :new.stid 可以直接得到即将存入数据库中的table1中的stid吗?
1、针对你这个需求是这里可以把BEFORE 改为AFTER
2、这里2个BEGIN 和 END,可以删除一对,但是BEGIN...EXCEPTION...END是配对的
3、触发器是通过:new关键字来获取新插入的纪录行的,因此:new.stid 可以直接得到即将存入数据库中的table1中的stid
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;请问,我这样写有问题吗? 为什么我插入了一条符合触发条件数据 ,为什么t_callthepolice这张表里没有相应的数据呢?
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;
'雨量超过警戒值'+:NEW.dayrainfall-v_rain
"+"号都改成“||”
--"+"号都改成“||”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;
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 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)
)
当我向data表里插入数据时,根据CALLTHEPOLICE表里的数据来判断是否触发触发器,如果触发了,就向
T_CALLTHEPOLICE表里新增一条数据。
还请多多帮忙, 或者可以加我QQ帮我远程看下, 谢谢。
QQ:513442676