In order to keep track of inserts and updates of records, i have added four columns to each table:

  • created_by = the user who created the record
  • creation_date = the date and time on which the record has been created
  • last_updated_by = the user who last updated the record
  • last_update_date = the date and time on which the record has been updated

Until now database triggers have been used to enter these fields. With Formspider that is not possible anymore: the user is always FORMSPIDER... The timestamps are still handled correctly by the trigger. However, i would prefer to enter these fields with somekind of PRE-INSERT of PRE-UPDATE trigger. Is that possible? How?

Kind regards, Jan Willem Vermeer

asked 07 Sep '15, 04:24

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231434
accept rate: 0%


Hi Jan,

Do you plan to use Formspider's user repository?(or any other user repository?) If so, you can use the current connected user name. You need to store the current user name once the user logs into the application.

Kind Regards,
Yalim

link

answered 07 Sep '15, 04:43

Yalim%20Gerger's gravatar image

Yalim Gerger ♦♦
1.8k5
accept rate: 15%

Hi Yalim,

I have the username in a session variable. But i do not know how i can automatically set its value to the columns of the datasource when the user inserts or updates the record.

For example... in Oracle Forms i would generate a pre-update trigger like this:

  • datasource-name.last_updated_by := api_session.getvaluetx('username');
  • datasource-name.last_update_date := sysdate;

I have not yet found something similar.

Kind regards, Jan Willem

link

answered 07 Sep '15, 04:56

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231434
accept rate: 0%

For example, you can do it in the table's before-update trigger.

(07 Sep '15, 05:00) Yalim Gerger ♦♦

Do you mean that there is something like a before-update trigger in Formspider? I can not find it. If you mean the database trigger - well, that's the problem. I only want to enter the username in the last_update_by when that user actually updates the record.

(07 Sep '15, 05:46) Jan Willem V...

You can use the table trigger. Since you are using a Formspider APi (or your own function that calls a Formspider API) it will be null if the incoming update is not originating from a Formspider application.

Another option is to use a view with an instead of trigger for update. In the instead of trigger you can modify the column values you want to.

Kind Regards,
Yalim

(08 Sep '15, 07:50) Yalim Gerger ♦♦

Hi Yalim,

I think the problem is not clear enough.

Triggers in the database can NOT be used because they only know the variable USER which is the database-user. And with Formspider applications that is always database-user FORMSPIDER.

I want to keep track of WHO updated or inserted a record. So I need the username of the logged in formspider-user. That username has been stored in a session value and when the formspider-user updates or inserts a record, that session value should be entered in the auditing fields.

Note: this can ONLY be done in the client application because that's the only place where the formspider-user is known.

In Oracle Forms this is very easy with the pre-insert and pre-update triggers. I am looking for similar functionality in Formspider. If it is not there, please help with an alternative.

Kind regards, Jan Willem

link

answered 08 Sep '15, 08:10

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231434
accept rate: 0%

Hi Jan,

I understand the issue. However, in Formspider we can cheat a little. :-) The Formspider client code runs in the database. So you have access to the logged-in Formspider user even in the trigger.

Granted, this violate perfect logical separation of server side and client side code but I think it doesn't hurt to be pragmatic in this case.

The second alternative (view with an instead of trigger) also holds. That's actually architecturally more sound. Logically, this view can be considered as part of the application code.

(08 Sep '15, 09:18) Yalim Gerger ♦♦

If you want the logical server side and the logical client side to be perfectly separated, then simply set a global variable (or a value/paramteer pair in a table, anything that works really...) to the current logged in user before you execute an api_application.doCommit and use this global variable in your trigger. This way you will not use a client code API in your server side code.

(08 Sep '15, 09:22) Yalim Gerger ♦♦

Hi Yalim,

so, if i understand you correctly, i could store the user in a session variable and call that variable in the database trigger. I will try that.

Kind regards, Jan Willem

(08 Sep '15, 10:28) Jan Willem V...

Hi,

the solution works ! Suppose the username has been stored in session variable "username", the trigger looks like below.

KR JW

create or replace TRIGGER <triggername>
 BEFORE INSERT
 ON <tablename>
 FOR EACH ROW
BEGIN
  :new.created_by       := nvl(api_session.getValueTX('username'),USER);
  :new.creation_date    := SYSDATE;
  :new.last_updated_by  := nvl(api_session.getValueTX('username'),USER);
  :new.last_update_date := SYSDATE;
END;
link

answered 12 Sep '15, 09:07

Jan%20Willem%20Vermeer's gravatar image

Jan Willem V...
1231434
accept rate: 0%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Tags:

×13
×9

Asked: 07 Sep '15, 04:24

Seen: 1,031 times

Last updated: 12 Sep '15, 09:07


© Copyright Gerger 2017