create or replace trigger commtools_all 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这里总报错
错误有很多,如:sqlserver里面有inserted updated deleted 表,oracle里面没有这个概念。 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]
[b]颜色没了。[/b]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;
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;