OraFAQ Forum: SQL & PL/SQL » trim field with trigger

Home » SQL & PL/SQL » SQL & PL/SQL » trim field with trigger

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

trim field with trigger [message #6977]

Wed, 14 May 2003 09:22

Hugh
Messages: 7
Registered: April 2002

Junior Member

Hi,

I have a sequence and a trigger that asign primary keys to new records in my table.
I now want to make sure that a field is trimmed when a record is inserted. I have tried the following but it does not work. Can anyone help?

CREATE SEQUENCE ACCIDENT_CASE_DATA_SEQ INCREMENT BY 1 START WITH 1;

CREATE OR REPLACE TRIGGER ACCIDENT_CASE_DATA_TRIGGER
BEFORE INSERT ON ACCIDENT_CASE_DATA
FOR EACH ROW
BEGIN
SELECT ACCIDENT_CASE_DATA_SEQ.NEXTVAL INTO :NEW.ID_ACCIDENT_CASE_DATA FROM DUAL;
UPDATE ACCIDENT_CASE_DATA set INJ_NAME = TRIM(INJ_NAME) where id_accident_case_data = (SELECT accident_case_data_SEQ.currval from dual);
END;
/

I get "Warning: Trigger created with compilation errors."

Report message to a moderator

Re: trim field with trigger [message #6980 is a reply to message #6977]

Wed, 14 May 2003 12:12

SaschaV
Messages: 18
Registered: February 2003

Junior Member

first of all U should type in SHOW ERRORS TRIGGER ACCIDENT_CASE_DATA_TRIGGER. U'll then have a better explanation of what went wrong.
But as far as I see, this cannot work, cuz:
- You select a new value from a sequence --> no entry in the DB yet
- You try to UPDATE a row in the DB that probably doesn't exists yet

Don't you have to do an INSERT first? Anyway, this is a BEFORE INSERT trigger, therefore the row isn't in the DB yet.

Why don't U just let the trigger do this:
CREATE OR REPLACE TRIGGER ACCIDENT_CASE_DATA_TRIGGER
BEFORE INSERT ON ACCIDENT_CASE_DATA
for each row
begin
:new.inj_name := trim(:new.inj_name);
:new.id_accident_case_data := ACCIDENT_CASE_DATA_SEQ.NEXTVAL;
end;
/
SHOW ERRORS TRIGGER ACCIDENT_CASE_DATA_TRIGGER

Hope this helps.
Sascha

Report message to a moderator

Re: trim field with trigger [message #6984 is a reply to message #6977]

Wed, 14 May 2003 12:43

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Sascha's reply is close, but the sequence still needs to be selected from dual:

create or replace trigger accident_case_data_trigger
before insert on accident_case_data
for each row
begin
  select accident_case_data_seq.nextval 
    into :new.id_accident_case_data 
    from dual;
 
  :new.inj_name = trim(:new.inj_name);
end;
/

Report message to a moderator

Re: trim field with trigger [message #7016 is a reply to message #6984]

Thu, 15 May 2003 08:04

Hugh
Messages: 7
Registered: April 2002

Junior Member

Hi,

Thanks for your solutions.
I found that Todd's solution almost worked. It required a colon before the equal sign in ":new.inj_name = trim(:new.inj_name);" so it became ":new.inj_name := trim(:new.inj_name);".

Can you possibly explain why the colon is required?
In fact what are the colons before 'new' for?

Thanks

Report message to a moderator

Re: trim field with trigger [message #7018 is a reply to message #7016]

Thu, 15 May 2003 12:06

Todd Barry
Messages: 4819
Registered: August 2001

Senior Member

Sorry about the missing colon - typo. The ':=' indicates assignment in PL/SQL.

The colons before new (:new) are to indicate that the reference is to the new value of the column. In update and delete row triggers, there are also :old values.

Report message to a moderator

Previous Topic:	Question on ROWNUM
Next Topic:	trigger in update or delete on 100+ tables w/ different owners.

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Tue Apr 23 13:01:03 CDT 2024