Hi All,
I have this trigger in almost all tables in a SCHEMA.
begin
if inserting then
if :NEW.APT_ID is null then
select COND_APARTAMENTOS_SEQ.NEXTVAL into :new.apt_id from sys.dual;
end if;
if :NEW.CREATED is null then
:NEW.CREATED := localtimestamp;
:NEW.CREATED_BY := NVL(v('APP_USER'),USER);
:NEW.UPDATED := localtimestamp;
:NEW.UPDATED_BY := NVL(v('APP_USER'),USER);
:NEW.ORGANIZACION_ID := v('APP_GET_ORGANIZACION_ID');
else
:NEW.CREATED_BY := ' ';
:NEW.UPDATED := :NEW.CREATED;
:NEW.UPDATED_BY := ' ';
end if;
end if;
if updating then
:NEW.UPDATED := localtimestamp;
:NEW.UPDATED_BY := NVL(v('APP_USER'),USER);
end if;
end;
The problem is that when inserting the fields CREATED_BY and UPDATED_BY are not getting updated and stays empty. The CREATED AND UPDATED do get their timestamp value.
I checked for CREATED not to be empty and is not.
Another thing is that I have the same tables in another SCHEMA and all triggers works fine there.
What could be causing these triggers in all tables not to work in this specific WORKSPACE when works in the other?
Also when updating the UPDATED_BY is getting updated with the same expression NVL(v('APP_USER'),USER) am using in the inserting so that value is not empty.
Any ideas or suggestion on what do I need to look for.
Thanks for you help on this!