translate from SQL Server to Oracle

Question

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:

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?

Answer 1

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