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
if :NEW.ID is null then
select USERID_SEQ.nextval into :NEW.ID from dual;
:NEW.TYPE := 'something else';
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.
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?
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.
Try this instead:
select 'something else' into :NEW.TYPE from dual;
If this syntax worked for
ID it should also work for