解决方案 »
- ORACLE中的百分号怎么用的????在线等
- ORA-31693 ORA-38500: Unsupported operation: Oracle XML DB not present
- 请教oracle存储过程
- 存储过程中使用变量
- 急求:oracle存取速度问题 (50分)
- select ltrim('abc_abc','_') from dual;
- 请介绍有关于oracle的sql语法的资料
- 请教:如何快速定位某个对象?立即结帐,谢谢!
- 有关两个Table数据的比较,SQL高手请关注^_^ 在线等待……
- oracle 11g dump file 导入 oracle 12c 后 表或者视图不存在
- ORACLE触发器检测不到 null字段?
- 完全删除表空间
after insert or update or delete on casemanage.commtools
for each row
declare
pareid number;
parecid number;
paretid number;
oldcommnum varchar(40) ;
newcommnum varchar(40) ;begin
--------新增
if exists(select * from inserted) and not exists(select * from deleted) then select c.cid into parecid from caseindex c,document d,inserted i where c.mid=i.mid and c.cid=d.cid ;
select i.tid into paretid from caseindex c,document d,inserted i where c.mid=i.mid and c.cid=d.cid ;
select i.id into pareid from caseindex c,document d,inserted i where c.mid=i.mid and c.cid=d.cid ;
if not exists(select id from infoissuelist where issuetype=4 and identifyid=pareid and operatetype=0) then
insert into InfoIssueList (identifyid,memo,issuetype,OperateType,Cid)
select id ,commnum,4,0,parecid from inserted ;
end if;
end if;
--------修改
if exists(select * from inserted) and exists(select * from deleted) then --and (update commnum)
select cid into parecid from caseindex where mid in(select mid from inserted) ;
select commnum into oldcommnum from deleted ;
select commnum into newcommnum from inserted ;
select tid into paretid from inserted ;
if (oldcommnum <> newcommnum) then
insert into InfoIssueList (identifyid,memo,issuetype,OperateType,Cid)
select id ,commnum,4,1,parecid from inserted ;
end if;
end if;
--------删除
if not exists(select * from inserted) and exists(select * from deleted) then
select id into pareid from deleted ;
if exists(select id from infoissuelist where issuetype=4 and identifyid=pareid and operateType=0) then select cid into parecid from caseindex where mid in(select mid from deleted) ;
insert into InfoIssueList (identifyid,memo,issuetype,OperateType,Cid)
select id ,commnum,4,2,parecid from deleted ;
end if;
end if;
end commtools_all;这是我oracle里的,现在exists这里总报错
oracle通过 :new 和 :old 来引用,我根据你写的代码,大致给你改了一下,你参考一下自己调试调试吧。
[code=SQL]
CREATE OR REPLACE TRIGGER COMMTOOLS_ALL
AFTER INSERT OR UPDATE OR DELETE ON CASEMANAGE.COMMTOOLS
FOR EACH ROWDECLARE
PAREID NUMBER;
PARECID NUMBER;
PARETID NUMBER;
OLDCOMMNUM VARCHAR(40);
NEWCOMMNUM VARCHAR(40);BEGIN
--------新增
--IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED) THEN
IF INSERTING THEN
SELECT C.CID,:NEW.TID,:NEW.ID
INTO PARECID,PARETID,PAREID
FROM CASEINDEX C, DOCUMENT D--, INSERTED I
WHERE C.MID = :NEW.MID--I.MID
AND C.CID = D.CID;
/*
SELECT I.TID
INTO PARETID
FROM CASEINDEX C, DOCUMENT D, INSERTED I
WHERE C.MID = I.MID
AND C.CID = D.CID;
SELECT I.ID
INTO PAREID
FROM CASEINDEX C, DOCUMENT D, INSERTED I
WHERE C.MID = I.MID
AND C.CID = D.CID;
*/
IF NOT EXISTS (SELECT ID
FROM INFOISSUELIST
WHERE ISSUETYPE = 4
AND IDENTIFYID = PAREID
AND OPERATETYPE = 0) THEN
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
SELECT ID, COMMNUM, 4, 0, PARECID FROM INSERTED;
END IF;
END IF; --------修改
--IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) THEN
--and (update commnum)
IF UPDATING THEN
SELECT CID
INTO PARECID
FROM CASEINDEX
WHERE MID =:NEW.MID--IN (SELECT MID FROM INSERTED)
;
/*
SELECT COMMNUM INTO OLDCOMMNUM FROM DELETED;
SELECT COMMNUM INTO NEWCOMMNUM FROM INSERTED;
SELECT TID INTO PARETID FROM INSERTED;
*/
--IF (OLDCOMMNUM <> NEWCOMMNUM) THEN
IF :OLD.COMMNUM <> :NEW.COMMNUM THEN
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
--SELECT ID, COMMNUM, 4, 1, PARECID FROM INSERTED;
SELECT ID, :NEW.COMMNUM, 4, 1, PARECID FROM DUAL;
END IF;
END IF; --------删除
--IF NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) THEN
IF DELETING THEN
--SELECT ID INTO PAREID FROM DELETED;
IF EXISTS (SELECT ID
FROM INFOISSUELIST
WHERE ISSUETYPE = 4
AND IDENTIFYID = :OLD.PAREID--PAREID
AND OPERATETYPE = 0) THEN
SELECT CID
INTO PARECID
FROM CASEINDEX
WHERE MID = :OLD.MID--IN (SELECT MID FROM DELETED)
;
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
--SELECT ID, COMMNUM, 4, 2, PARECID FROM DELETED;
SELECT ID, :OLD.COMMNUM, 4, 2, PARECID FROM DUAL;
END IF;
END IF;
END COMMTOOLS_ALL;[code]
AFTER INSERT OR UPDATE OR DELETE ON CASEMANAGE.COMMTOOLS
FOR EACH ROWDECLARE
PAREID NUMBER;
PARECID NUMBER;
PARETID NUMBER;
OLDCOMMNUM VARCHAR(40);
NEWCOMMNUM VARCHAR(40);BEGIN
--------新增
--IF EXISTS (SELECT * FROM INSERTED) AND NOT EXISTS (SELECT * FROM DELETED) THEN
IF INSERTING THEN
SELECT C.CID,:NEW.TID,:NEW.ID
INTO PARECID,PARETID,PAREID
FROM CASEINDEX C, DOCUMENT D--, INSERTED I
WHERE C.MID = :NEW.MID--I.MID
AND C.CID = D.CID;
/*
SELECT I.TID
INTO PARETID
FROM CASEINDEX C, DOCUMENT D, INSERTED I
WHERE C.MID = I.MID
AND C.CID = D.CID;
SELECT I.ID
INTO PAREID
FROM CASEINDEX C, DOCUMENT D, INSERTED I
WHERE C.MID = I.MID
AND C.CID = D.CID;
*/
IF NOT EXISTS (SELECT ID
FROM INFOISSUELIST
WHERE ISSUETYPE = 4
AND IDENTIFYID = PAREID
AND OPERATETYPE = 0) THEN
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
SELECT ID, COMMNUM, 4, 0, PARECID FROM INSERTED;
END IF;
END IF; --------修改
--IF EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) THEN
--and (update commnum)
IF UPDATING THEN
SELECT CID
INTO PARECID
FROM CASEINDEX
WHERE MID =:NEW.MID--IN (SELECT MID FROM INSERTED)
;
/*
SELECT COMMNUM INTO OLDCOMMNUM FROM DELETED;
SELECT COMMNUM INTO NEWCOMMNUM FROM INSERTED;
SELECT TID INTO PARETID FROM INSERTED;
*/
--IF (OLDCOMMNUM <> NEWCOMMNUM) THEN
IF :OLD.COMMNUM <> :NEW.COMMNUM THEN
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
--SELECT ID, COMMNUM, 4, 1, PARECID FROM INSERTED;
SELECT ID, :NEW.COMMNUM, 4, 1, PARECID FROM DUAL;
END IF;
END IF; --------删除
--IF NOT EXISTS (SELECT * FROM INSERTED) AND EXISTS (SELECT * FROM DELETED) THEN
IF DELETING THEN
--SELECT ID INTO PAREID FROM DELETED;
IF EXISTS (SELECT ID
FROM INFOISSUELIST
WHERE ISSUETYPE = 4
AND IDENTIFYID = :OLD.PAREID--PAREID
AND OPERATETYPE = 0) THEN
SELECT CID
INTO PARECID
FROM CASEINDEX
WHERE MID = :OLD.MID--IN (SELECT MID FROM DELETED)
;
INSERT INTO INFOISSUELIST
(IDENTIFYID, MEMO, ISSUETYPE, OPERATETYPE, CID)
--SELECT ID, COMMNUM, 4, 2, PARECID FROM DELETED;
SELECT ID, :OLD.COMMNUM, 4, 2, PARECID FROM DUAL;
END IF;
END IF;
END COMMTOOLS_ALL;