关于查询安插以及查询更新实例sql&触发器示例
工作札记
查询插入：
insert into tempdevice(dpath,dname) select ttt1.devicepath,ttt1.displayname
from tblnmdevice ttt1
join tblbusinessdeviceunion ttt2 on ttt1.devicepath =
ttt2.devicepath
join tblbusinesssys ttt3 on ttt3.tbluuid = ttt2.businessid
where ttt3.systemcode = '/NM_Topo/A33' ;
查询更新：
update tblnmdevice tt1
set tt1.displayname = (select t1.displayname||'路由器'
from tblnmdevice t1
where t1.devicepath = tt1.devicepath)
where tt1.devicepath in
(select ttt1.devicepath
from tblnmdevice ttt1
join tblbusinessdeviceunion ttt2 on ttt1.devicepath =
ttt2.devicepath
join tblbusinesssys ttt3 on ttt3.tbluuid = ttt2.businessid
where ttt3.systemcode = '/NM_Topo/A33' and ttt1.displayname not like '%路由器' and ttt3.pid is not null)
;
触发器示例：
prompt
prompt Creating trigger aiud_nmdevice
prompt ======================================
prompt
CREATE OR REPLACE TRIGGER aiud_nmdevice
AFTER INSERT OR UPDATE OR DELETE ON tblnmdevice
FOR EACH ROW
DECLARE
v_count Number(20);
v_tablename VARCHAR(36) := 'Tblnmdevice';
v_uploadtype VARCHAR(50) := 'upload_actionlog';
v_primaryKey VARCHAR(36) := 'devicepath';
BEGIN
IF inserting THEN
IF :new.operationstatus IS NULL THEN
INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate,dataname)
VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '1', v_tablename, :new.devicepath, SYSDATE, '1', v_primaryKey);
END IF;
ELSIF updating THEN
SELECT COUNT(log.logid) INTO v_count FROM tbluploadactionlog log WHERE log.dataid =:new.devicepath AND log.actiontype ='2';
IF v_count = 0 THEN
INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate, dataname)
VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '2', v_tablename, :new.devicepath, SYSDATE, '1', v_primaryKey);
ELSIF v_count <> 0 THEN
UPDATE tbluploadactionlog actionlog SET actionlog.actiontime = SYSDATE
WHERE actionlog.dataid =:new.devicepath AND actionlog.actiontype='2';
END IF;
ELSIF deleting THEN
IF :old.operationstatus IS NULL THEN
INSERT INTO tbluploadactionlog (logid, logtype, actiontype, tablename, dataid, actiontime, actionstate, dataname)
VALUES (SEQ_UPLOADACTION.Nextval, v_uploadtype, '3', v_tablename, :old.devicepath, SYSDATE, '1', v_primaryKey);
END IF;
END IF;
END aiud_nmdevice;
/