CODEWITHSUNDEEP
plsqltriggersoracle11g
Hohenheimsenberg
Hohenheimsenberg

Reputation: 980

Value of a NEW variable on a trigger not changing, on plsql

I'm loading data through Oracle Apex utilities using a datasheet.

I want to make a trigger that checks for a value on the table from the data loaded, and then changes it depending on what it gets.

The table has 4 columns: id,name,email,type

The data to load is something like this: name,email,type

Now my trigger:

create or replace TRIGGER  BI_USER
before insert ON USER               
for each row  

declare
begin   
  if :NEW.ID is null then
    select USERID_SEQ.nextval into :NEW.ID from dual;
  end if;
 :NEW.TYPE := 'something else';
end;

The ID works great, it takes a number from the sequence, but :new.type isn't working, it doesn't change. I also run the SQL insert separately and the same happens.

EDIT: new.type type is char(1), I wrote it like this just for testing yet it doesn't change... aah I'm dissapoint of myself, it throws the error just after reading the data and never fires the trigger.

What I was trying to do is that it will have the name of the TYPE column, and put the id from that table into the NEW.type

Is there a way to change the NEW type?

Upvotes: 1

Views: 1418

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60272

I see what you're trying to do. You want your table to accept an inserted record containing data that will not fit in the width of one of the fields, and you want to use a trigger to "fix" the data so that it will fit.

Unfortunately, this trigger will not help you because the data is validated before your triggers are fired.

An alternative way to get around this may be to use a view with an instead-of trigger. The view would have a column "TYPE" which is based on a string of length 9; the instead-of trigger would convert this to the CHAR(1) for insert into the underlying table.

Upvotes: 2

Bohemian
Bohemian

Reputation: 425083

Try this instead:

select 'something else' into :NEW.TYPE from dual;

If this syntax worked for ID it should also work for TYPE

Upvotes: 0

Related Questions