我想在一个表插入数据的时候更新另一个表,采用触发器的方式,但是现在插入的时候无法更新另一个表。恳请大侠们帮忙触发器代码如下:
create or replace trigger vehicle_gps_history_triger
after insert ON gps_history
for each row
Declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update gps_info
set LONGITUDE = :NEW.LONGITUDE,
LATITUDE = :NEW.LATITUDE,
KPM = :NEW.KPM,
ANGLE = :NEW.ANGLE,
MSG_REC_TIME = :NEW.MSG_REC_TIME,
VEHICLE_NUM = :NEW.VEHICLE_NUM
where vehicle_id = :NEW.vehicle_id;
end;
create or replace trigger vehicle_gps_history_triger
after insert ON gps_history
for each row
Declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update gps_info
set LONGITUDE = :NEW.LONGITUDE,
LATITUDE = :NEW.LATITUDE,
KPM = :NEW.KPM,
ANGLE = :NEW.ANGLE,
MSG_REC_TIME = :NEW.MSG_REC_TIME,
VEHICLE_NUM = :NEW.VEHICLE_NUM
where vehicle_id = :NEW.vehicle_id;
end;
before insert ON gps_history for each row
begin
update gps_info
set LONGITUDE = :NEW.LONGITUDE,
LATITUDE = :NEW.LATITUDE,
KPM = :NEW.KPM,
ANGLE = :NEW.ANGLE,
MSG_REC_TIME = :NEW.MSG_REC_TIME,
VEHICLE_NUM = :NEW.VEHICLE_NUM
where vehicle_id = :NEW.vehicle_id;
end;--也可以这样,但不支持下面的
create or replace trigger vehicle_gps_history_triger
before insert ON gps_history
for each row
Declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update gps_info
set LONGITUDE = :NEW.LONGITUDE,
LATITUDE = :NEW.LATITUDE,
KPM = :NEW.KPM,
ANGLE = :NEW.ANGLE,
MSG_REC_TIME = :NEW.MSG_REC_TIME,
VEHICLE_NUM = :NEW.VEHICLE_NUM
where vehicle_id = :NEW.vehicle_id;
commit;
end;
Create or replace trigger vehicle_gps_history_triger
before insert ON gps_history --要用before
for each row
Declare
PRAGMA AUTONOMOUS_TRANSACTION;
begin
update gps_info
set LONGITUDE = :NEW.LONGITUDE,
LATITUDE = :NEW.LATITUDE,
KPM = :NEW.KPM,
ANGLE = :NEW.ANGLE,
MSG_REC_TIME = :NEW.MSG_REC_TIME,
VEHICLE_NUM = :NEW.VEHICLE_NUM
where vehicle_id = :NEW.vehicle_id;
end;
楼主查看下:插入到gps_history中的vehicle_id在
gps_info表中是否有数据
create or replace trigger dept_log_tri
before insert on dept
for each row
begin
update deptlog set
deptnolog=:new.deptno,
dnamelog=:new.dname,
loclog=:new.loc;
end;
SQL> select * from deptlog;--先查询deptlog表为空未选定行SQL> insert into dept values(60,'nanchang','jx');已创建 1 行。SQL> commit;提交完成。SQL> select * from deptlog;--往dept表中插入数据后查询deptlog表还是为空(你现在的情况)未选定行SQL> insert into deptlog values(60,'nanchang','jx');--往deptlog表中插入数据已创建 1 行。SQL> commit;提交完成。SQL> insert into dept values(70,'jiujiang','jx');--再往dept表中插入数据已创建 1 行。SQL> commit;提交完成。SQL> select * from deptlog;--得到你要的结果了 DEPTNOLOG DNAMELOG LOCLOG
---------- -------------- -------------
70 jiujiang jx
可以我要更新的数据,在gps_info已经存在了