问题描述:

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?

网友答案:

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 TYPE

相关阅读:
Top