开发者

translate from SQL Server to Oracle

I was hoping one of you Oracle experts would be able to give me a hand with this. I have the following SQL Server script, but I need to rewrite it for Oracle:

开发者_JAVA技巧USE mydb
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE trigger mydb . [CONNECTERTRIGGER] on mydb . [DtreeNotify]
FOR INSERT AS
BEGIN

IF @@ROWCOUNT=0
  RETURN

SET IDENTITY_INSERT mydb.DTreeNotify2 ON

INSERT INTO mydb.DTreeNotify2 
   (NID,NType,DataID,VersionNum,OwnerID,SubType)
SELECT inserted.NID, 
       inserted.NType, 
       inserted.DataID,
       inserted.VersionNum,
       mydb.Dtree.OwnerID, 
       livelink.DTree.SubType
  FROM inserted, livelink.DTree
 WHERE inserted.DataID = livelink.DTree.DataID;

END

I think @@rowcount becomes sql%rowcount, but I'm struggling with the identity_insert bit. I don't think anything else should change. Opinions?


Don't worry about the IDENTITY_INSERT bit, the way it is done in Oracle is so much different that there is no need for such an option anyway. Look for "SEQUENCE" to learn more about that.

Here we go:

CREATE trigger "CONNECTERTRIGGER"
  AFTER INSERT on "DtreeNotify"
  FOR EACH ROW
begin
  insert into DTreeNotify2 (NID,NType,DataID,VersionNum,OwnerID,SubType)
    select :new.NID, :new.NType, :new.DataID, :new.VersionNum,
           Dtree.OwnerID, livelink.DTree.SubType
      from livelink.DTree
      where :new.DataID=livelink.DTree.DataID;
end;

Comments: I assume Dtree.OwnerID is a package variable you can read directly. As you can see, there is a :new record, which contains one inserted record (this trigger is called for each record that gets inserted).

Edit: changed the BEFORE trigger to an AFTER trigger

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜