newbie: trigger to modify rows going into a table

I come from a Sybase / SQL*Server background, so I'm still learning how to develop a Sybase trigger. I'm trying to figure out how to create a proper Oracle trigger for some specific scenarios.

Here's a simplified problem I need to solve, and I've frankly been having problems figuring out the right way to get it done in Oracle.

Let's say I have the following table:

        CREATE OR REPLACE TABLE UserData 


        (


                user_id         integer  NOT NULL,
                username        varchar(20) NOT NULL,
                chg_date        date   NOT NULL
        );

(Consider user_id the primary key for the table.)

I would like to have a trigger(s) which:

1. sets the chg_date to the current date on INSERT or UPDATE
2. if the user_id inserted is below 100, the username inserted will have the text "a_" prepended to it. (this would only be on an INSERT)

(For this made up scenario, don't worry about extreme case situations, such as if someone inserts a username which is exactly 20 characters and then the trigger tries prepend the "a_" and this has a problem, etc, etc, etc. I'm really just trying to get a better grasp on getting the trigger to work in a general case, not all cases right now.)

Here's an example:

        INSERT INTO UserData
        SET


        (


                username,
                user_id
        )
        VALUES


        (


                'jeff', 
                10
        );

In this situation, I would like the final data to look like that below:

        USERNAME        USER_ID         CHG_DATE
        a_jeff          10              21-MAY-1999

Most of the attempts to create this trigger either cause "out of cursors" errors, or problems with trying to access data in the table that's currently "mutating".

An help is appreciated, and any responses sent directly to me I will repost the information for all to learn from.

Thanks.

Jeff Braun Received on Fri May 21 1999 - 17:46:17 CDT