sql trigger to oracle trigger
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 2 of 2

Thread: sql trigger to oracle trigger

Hybrid View

  1. #1
    Join Date
    Oct 2002
    Posts
    8

    sql trigger to oracle trigger

    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

  2. #2
    Join Date
    Apr 2002
    Location
    Philippines
    Posts
    77
    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.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width