Re: ASP & SQL - Very urgent please!

Judith,

It looks as though you want to use "instead of insert" and not "before insert", otherwise recursion is likely.
The other option is to just make changes to the parameters and let the original insert use the modified values.

For instance:

create or replace trigger trigger_name
before insert or update of column1 on mytable for each row
begin
  :new.column1 := UPPER(:new.column1);
end;

The above would just make sure the new value was uppercase before allowing the original insert to complete. You don't actually provide the insert code here, unless you wish to insert into another table.

create or replace trigger trigger_name
instead of insert on mytable
for each row
begin
  insert into mytable (column0, column1, ...) values (:new.column0, UPPER(:new.column1), ...);
end;

Regards... Jon

• Original Message ----- From: "Judith" <judithlemo_at_hotmail.com> Newsgroups: comp.databases.oracle Sent: Monday, March 18, 2002 3:31 PM Subject: ASP & SQL - Very urgent please!

> create or replace trigger makeSequence
> before insert on member
> for each row
> begin
> insert into member values (NEWMEMBERSEQ.nextval, :new.MEMBERSHIP_TYPE,
> :new.FIRSTNAME, :new.MIDDLENAME, :new.LASTNAME,
> :new.ADDRESS, :new.CITY, :new.POSTCODE, :new.TELEPHONE, :new.E_MAIL,
> :new.DATE_OF_BIRTH, :new.START_DATE, :new.INTEREST,
> :new.OBJECTIVE, :new.CURRENT_LEVEL, :new.STATUS, :new.PASSWORD,
> :new.VERIFY_PASSWORD, :new.PAYMENTMETHOD, :new.PERSONAL_TRAINER_ID,
> :new.MEMBERSHIP_ID);
> end;
> /
Received on Tue Mar 19 2002 - 08:25:38 CET