Re: newbie: trigger to modify rows going into a table

A copy of this was sent to jeffb_at_halcyon.com (Jeffrey Mark Braun) (if that email address didn't require changing) On 21 May 1999 15:46:17 -0700, you wrote:

>
>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

create or replace trigger maintain_chg_date before insert or update on userData
for each row
begin

   :new.chg_date := sysdate;
end;
/

> 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)
>

create or replace trigger maintain_username before insert on userData
for each row
begin

    if ( :new.user_id < 100 ) then

       :new.username := 'a_' || :new.username;     end if;
end;
/

>(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

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA

--
Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Fri May 21 1999 - 19:52:03 CDT