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
精彩评论