Trigger ?

[Sofiane]

Hello,
Suppose that you have a table T1 with columns C11, C12....
When a new line is inserted, I would like modify the value of the field C11 if a given condition is satisfied : If C11 like '-%',
I would like to add 'P' in the bgining of the C11 field :
concat('P', OLD_C11_VALUE).

Can I do that with a trigger and How ?

Thanks a lot in advance.

Sofiane

[marist89]

Sure, a trigger is the perfect method for that. You will be using a before insert trigger. In the body of the trigger, you can inspect the c11 value and change it before it is inserted in the table.

[marist89]

Something like (syntax foggy, but you get the idea):

create or replace trigger bi_t1
before insert on t1 -- you are specifying you want this to fire before the insert happens
for each row -- check every row
begin
if C11 = '-' then
:new.C11 = 'P' || C11; -- assign the value using the builtin :new
end;
end;

[Sofiane]

Hello,

Thanks a lot for the reply, the right syntax is the following :


CREATE OR REPLACE trigger bi_t1
BEFORE INSERT ON T1
FOR EACH RAW
BEGIN
IF (:NEW.C11 LIKE '-%') THEN
:NEW.C11:=concat('P',:C11);
END IF;
END;

Sofiane

[Sofiane]

Sorry, the right syntax is the following :


CREATE OR REPLACE trigger bi_t1
BEFORE INSERT ON T1
FOR EACH RAW
BEGIN
IF (:NEW.C11 LIKE '-%') THEN
:NEW.C11:=concat('P',:NEW.C11);
END IF;
END;