sql trigger to oracle trigger

[sneha]

i have a code in sql and wants to do the same kind of thing in oracle,can anybody help me,

CREATE TRIGGER tIU_tblSecApplicationUsers ON dbo.tblSecApplicationUsers
FOR INSERT, UPDATE
AS
RETURN
DECLARE @UserID INT
DECLARE @LoginID VARCHAR(25)
DECLARE @FirstName VARCHAR(25)
DECLARE @MiddleName VARCHAR(25)
DECLARE @LastName VARCHAR(25)


SELECT @UserID = UserID, @LoginID = AppUserName, @FirstName = FirstName, @MiddleName = MiddleName, @LastName = LastName FROM INSERTED

IF UPDATE(AppUserName) OR UPDATE(FirstName) OR UPDATE(MiddleName) OR UPDATE(LastName)

BEGIN

IF EXISTS (SELECT * FROM Users WHERE UserID = @UserID)

BEGIN
UPDATE Users SET

LogonID = @LoginID,
FirstName = @FirstName,
MiddleName = @MiddleName,
LastName = @MiddleName
WHERE UserID =@UserID

END
ELSE
BEGIN

INSERT INTO Users (
LogonID,
FirstName,
MiddleName,
LastName,
UserID
)
VALUES (

@LoginID,
@FirstName,
@MiddleName,
@LastName,
@UserID
)


UPDATE users SET UserId = @UserID WHERE LogonID = @loginID

END

END

especially how to check 'if exists' kind of thing in oracle and 'from inserted'.
please help me

[skid]

sneha,

For the "if exists" you could try the when no_data_found exception:

BEGIN
SELECT *
FROM Users
WHERE UserID = @UserID;
... -- update codes here
EXCEPTION
WHEN NO_DATA_FOUND THEN
... -- insert codes here
END;

For "from inserted" you can use the :NEW qualifier like :NEW.UserID, :NEW.AppUserName, :NEW.FirstName, etc. You would assign the variables to this values.


Check the Oracle Docs for more info on triggers.

hth.