This content has been marked as final. Show 11 replies
Do it in a table trigger...
This will allow you to cover data being inserted by an APEX app or someone using sqlplus/toad/sql Developer.. (the $IF $$APEX $THEN.. )
reate or replace trigger sample_trigger before insert on sample_table for each row begin select sample_table_seq.nextval into :new.id from dual; :new.time_stamp := systimestamp; $IF $$APEX $THEN :new.user_name := nvl(v('APP_USER'),user); $ELSE :new.user_name := user; $END end;
It can be enhanced to also handle updates to the table too!!
This code runs in the database, when ever you INSERT a row into the named table.. You REALLY should read up on the database before trying to build APEX applications.. It will help you to understand database development concepts..
Also you REALLY should ook at updating your instance of APEX, since 3.0 is NOT a supported product at this point in time..
True Steve, I feel your pain with regards to what the client is using but Tony has a very valid point about upgrading their version Apex.
You might be able to convince them to upgrade by doing some nice demos, and more importantly, reminding them by moving their legacy system to Apex 3, they'd be in a certain sense be moving from one Legacy system to another.
I understand how to create a trigger, I dont understand the syntax
In this case the trigger is an update, not an insert, when the user updates any field on the record, I want to add the timestamp and the user ID
I took your insert example and tried to create an update trigger.
the columns are called updatedby and updatedate
If I do this:
create or replace
before update of updatedby on Employee
$IF $$APEX $THEN
:updatedby := nvl(v('APP_USER'),user);
:updatedby := user;
the trigger runs without error, and says it is valid. But when I update a record in the application, nothing happens.
I've tried before update and after update
If I try to create the trigger use the updatedate column ie
:updatedate := systimestamp;
I get a bind variable error message and can't create the trigger
I've seen other examples online that use a set columnName = value statement, like I would usually do in an update, but I can't get that to work either, always get syntax errors when I try
what I did initial was not using triggers
I created an item for timestamp, no problem , I can always get systimestamp
I created something that worked at captured the user name the first time they updated a record:
v varchar2(255) := null;
c := owa_cookie.get('LOGIN_USERNAME_COOKIE');
:P3_TEMP_USER := c.vals(1);
exception when others then null;
but if they update a second record, the value is gone
isn't there some way to save this value in an item ? Ive read about application items vs page items, but doesnt seem that Apex 3.0 has application items
if I do this:
$IF $$APEX $THEN
:NEW.Updated_by := nvl(v('APP_USER'),user);
:NEW.Update_by := user;
I get an error message: PLS-00049: bad bind variable 'NEW.Update_by' pointing to the $ELSE portion
If I take out the if /else and just do this:
:NEW.Updatedby := nvl(v('APP_USER'),user);
I can live with that, I understand you're point about Apex users vs sql developer, etc
thanks for your help