mysql触发器转成oracle触发器 求助

[lzp982163212]

这是sqlserver的触发器 我需要把它用oracle的触发器语法写出来 求大神援助

BEGIN
DECLARE @VSYS_DOCUMENTID INT,@ISYS_DOCUMENTID INT
SELECT @VSYS_DOCUMENTID = [SYS_DOCUMENTID] FROM DELETED
SELECT @ISYS_DOCUMENTID = [SYS_DOCUMENTID] FROM INSERTED
DECLARE @NDEL INT,@NINS INT
SELECT @NDEL = COUNT(*) FROM DELETED
SELECT @NINS = COUNT(*) FROM INSERTED
BEGIN
IF @NDEL>0
BEGIN
IF EXISTS (SELECT "SYS_DOCUMENTID" FROM [INCRE_DOM_REL_SEARCH] WHERE ( ([SYS_DOCUMENTID]=@VSYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @VSYS_DOCUMENTID is NULL ) ) ))
BEGIN
DELETE FROM [INCRE_DOM_REL_SEARCH] WHERE ([SYS_DOCUMENTID]=@VSYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @VSYS_DOCUMENTID is NULL ) ) AND FTAG = 1 AND FRES = 0
IF EXISTS( SELECT FTAG FROM [INCRE_DOM_REL_SEARCH] WHERE ([SYS_DOCUMENTID]=@VSYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @VSYS_DOCUMENTID is NULL ) ) AND FTAG=1 AND FRES=1)
INSERT INTO [INCRE_DOM_REL_SEARCH]("SYS_DOCUMENTID", FTAG,FRES) VALUES (@VSYS_DOCUMENTID,2,0)
UPDATE [INCRE_DOM_REL_SEARCH] SET FTAG = 2 WHERE ( ([SYS_DOCUMENTID]=@VSYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @VSYS_DOCUMENTID is NULL ) ) AND FTAG=3 AND FRES=0)
IF EXISTS( SELECT FTAG FROM [INCRE_DOM_REL_SEARCH] WHERE ([SYS_DOCUMENTID]=@VSYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @VSYS_DOCUMENTID is NULL ) ) AND FTAG=3 AND FRES=1)
INSERT INTO [INCRE_DOM_REL_SEARCH]("SYS_DOCUMENTID", FTAG,FRES) VALUES (@VSYS_DOCUMENTID,2,0)
END
ELSE INSERT INTO [INCRE_DOM_REL_SEARCH]("SYS_DOCUMENTID",FTAG,FRES) VALUES(@VSYS_DOCUMENTID,2,0)
END
IF @NINS>0
BEGIN
IF EXISTS(SELECT "SYS_DOCUMENTID" FROM [INCRE_DOM_REL_SEARCH] WHERE ([SYS_DOCUMENTID]=@ISYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @ISYS_DOCUMENTID is NULL ) ) )
BEGIN
UPDATE [INCRE_DOM_REL_SEARCH] SET FTAG = 3 WHERE ( ([SYS_DOCUMENTID]=@ISYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @ISYS_DOCUMENTID is NULL ) ) AND FTAG =2 AND FRES =0)
IF EXISTS ( SELECT FTAG FROM [INCRE_DOM_REL_SEARCH] WHERE ([SYS_DOCUMENTID]=@ISYS_DOCUMENTID OR ([SYS_DOCUMENTID] is NULL AND @ISYS_DOCUMENTID is NULL ) ) AND FTAG=2 AND FRES = 1)
INSERT INTO [INCRE_DOM_REL_SEARCH]("SYS_DOCUMENTID",FTAG,FRES) VALUES (@ISYS_DOCUMENTID,1,0)
END
ELSE
INSERT INTO [INCRE_DOM_REL_SEARCH]("SYS_DOCUMENTID",FTAG,FRES) VALUES(@ISYS_DOCUMENTID,1,0)
END
END
END

[lzp982163212]

create or replace trigger FT_DOM_REL_SEARCH after insert OR UPDATE OR DELETE on dom_rel_search for each row declare -- local variables here VSYS_DOCUMENTID number; ISYS_DOCUMENTID number; NDEL number; nins number; begin case when inserting then VSYS_DOCUMENTID:=:new."SYS_DOCUMENTID"; SELECT COUNT(*) into NDEL from DOM_REL_SEARCH; when deleting then ISYS_DOCUMENTID:=:new."SYS_DOCUMENTID"; SELECT COUNT(*) into nins from DOM_REL_SEARCH; END case; IF NDEL>0 THEN IF EXISTS(SELECT "SYS_DOCUMENTID" FROM "INCRE_DOM_REL_SEARCH" WHERE ( ("SYS_DOCUMENTID"=VSYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND VSYS_DOCUMENTID is NULL ) ) )) THEN DELETE FROM "INCRE_DOM_REL_SEARCH" WHERE ("SYS_DOCUMENTID"=VSYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND VSYS_DOCUMENTID is NULL ) ) AND FTAG = 1 AND FRES = 0 ; ELSIF EXISTS(SELECT FTAG FROM "INCRE_DOM_REL_SEARCH" WHERE("SYS_DOCUMENTID"=VSYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND VSYS_DOCUMENTID is NULL ) ) AND FTAG=1 AND FRES=1) THEN INSERT INTO "INCRE_DOM_REL_SEARCH"("SYS_DOCUMENTID", FTAG,FRES) VALUES (VSYS_DOCUMENTID,2,0); UPDATE "INCRE_DOM_REL_SEARCH" SET FTAG = 2 WHERE ( ("SYS_DOCUMENTID"=VSYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND VSYS_DOCUMENTID is NULL ) ) AND FTAG=3 AND FRES=0); ELSIF EXISTS(SELECT FTAG FROM "INCRE_DOM_REL_SEARCH" WHERE ("SYS_DOCUMENTID"=VSYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND VSYS_DOCUMENTID is NULL ) ) AND FTAG=3 AND FRES=1) THEN INSERT INTO "INCRE_DOM_REL_SEARCH"("SYS_DOCUMENTID", FTAG,FRES) VALUES (VSYS_DOCUMENTID,2,0); END IF; ELSE INSERT INTO "INCRE_DOM_REL_SEARCH"("SYS_DOCUMENTID", FTAG,FRES) VALUES (VSYS_DOCUMENTID,2,0); END IF; IF NINS>0 THEN IF EXISTS(SELECT "SYS_DOCUMENTID" FROM "INCRE_DOM_REL_SEARCH" WHERE ("SYS_DOCUMENTID"=ISYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND ISYS_DOCUMENTID is NULL ) ) ) THEN UPDATE "INCRE_DOM_REL_SEARCH" SET FTAG = 3 WHERE ( ("SYS_DOCUMENTID"=ISYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND ISYS_DOCUMENTID is NULL ) ) AND FTAG =2 AND FRES =0); ELSIF EXISTS ( SELECT FTAG FROM "INCRE_DOM_REL_SEARCH" WHERE ("SYS_DOCUMENTID"=ISYS_DOCUMENTID OR ("SYS_DOCUMENTID" is NULL AND ISYS_DOCUMENTID is NULL ) ) AND FTAG=2 AND FRES = 1) THEN INSERT INTO "INCRE_DOM_REL_SEARCH"("SYS_DOCUMENTID",FTAG,FRES) VALUES (ISYS_DOCUMENTID,1,0); END IF; ELSE INSERT INTO "INCRE_DOM_REL_SEARCH"("SYS_DOCUMENTID",FTAG,FRES) VALUES(ISYS_DOCUMENTID,1,0); END IF; END FT_DOM_REL_SEARCH; 自己抠出来了.. 还是自己靠谱