This can be done quite easily assuming that you are also the programmer of the application.

When designing the table, just add two fields:
- active_app char(5)
- last_app char(5)

On the client end, for example, if you have two client applications: POS.EXE and AUDIT.EXE

In POS client application, just be sure you always assign:
DataSet['ACTIVE_APP'] := 'POS' (whether it is insert or update.

In AUDIT client application, just be sure you always assign:
DataSet['ACTIVE_APP'] := 'AUDIT'

So, when the POS does the insert or update, the server is sure to know this because ACTIVE_APP = 'POS', and same with AUDIT because ACTIVE_APP will be 'AUDIT'

BACK TO THE SERVER SIDE:

Create the following TRIGGERS:

CREATE TRIGGER InsApp_MyTable for MyTable
BEFORE INSERT /*(OR UPDATE)*/
AS
BEGIN
IF (ACTIVE_APP IS NOT NULL) THEN
BEGIN
IF (ACTIVE_APP='POS') THEN
BEGIN
--> place the pos code here
END
ELSE IF (ACTIVE_APP='AUDIT') THEN
BEGIN
--> place code for audit changes here
END
LAST_APP=ACTIVE_APP;
ACTIVE_APP=NULL
END

Note that in this technique we always reset the ACTIVE_APP back to NULL, but if you want to know which application did the last change or the initial insert you pass the ACTIVE_APP value to the LAST_APP field before setting ACTIVE_APP to null.

If you want to be more thorough, you can have INS_APP and UPD_APP instead of LAST_APP where on the INSERT trigger you say INS_APP=ACTIVE APP and on the UPDATE Trigger you say UP_APP=ACTIVE_APP.

I find this technique useful for databases with INTER-DEPARTMENTAL client applications. For example, the Consumer Services Department might log in a complaint using the CSD client Application, while the Technical Department client might act on the complaint, and another department will have to deal with other steps. By keeping track of which client application has last dealt with the record, you have an automatic STATUS report on the flow.

Another use is for example, if the client application is the POS application, then certain changes could be made and totals can be redone. However, once the client application is AUDIT or MANAGER, the record can be locked so that it cannot be changed.

if you want an even more detailed trail you can include INSERT_USER, INSERT_TIME, UPDATE_USER, AND UPDATE_TIME columns to these crucial tables so that you not only know which application has inserted or last touched the record (row), but also which user and when.

Joseph Alba
jalba313@...








Nando Dessena <nando@...> wrote:
Ann,

>> ... replication ... where you don't want some of
>> the target's database insert/update triggers to fire for various
>> reasons ... done by checking CURRENT_USER and reserving
>> special user names to the tools (like the replicator).

A> The advantage of checking the user (or role) rather than the application
A> is that there's no way to insure that an application is what it claims
A> to be

sure, but that depends on how you implement it. The concept itself is
not secure or insecure. You could restrict the use of an application
name only to those to which you have granted it, like you do with
roles. Would that be a duplication of the role concept? For the most
part, yes. As I said I find the current tools adequate already.

Ciao
--
Nando Dessena
http://www.flamerobin.org
======================================================
I support Firebird, I am a Firebird Foundation member!
Join today at http://www.firebirdsql.org/ff/foundation
======================================================



---------------------------------
Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to the Yahoo! Terms of Service.



---------------------------------
Do you Yahoo!?
Yahoo! Mail - Helps protect you from nasty viruses.

[Non-text portions of this message have been removed]