why does not this work?

[Apprentice101]

this is driving me nuts!
I have it on a preinsert trigger at the form level.
it does not give me any errors, it just does not write anything on the auditing table at all!.

-------------------------------------------------------
DECLARE

v_block VARCHAR2(30);
v_username VARCHAR2(30);
v_date DATE;
v_item varchar(30);

BEGIN

COPY(v_date, v_block||'auditing_inserts.inserted_date');
COPY(v_username, v_block||'auditing_inserts.inserted_by');
COPY(v_item, 'auditing_inserts.item_inserted');
COPY(v_block,'auditing_inserts.block_name');
END;
-------------------------------------------------------
I also tried it like this, and the same results, NADA.

DECLARE

v_block VARCHAR2(30);
v_username VARCHAR2(30);
v_date DATE;
v_item varchar(30);

BEGIN
:auditing_inserts.inserted_date := v_date;
:auditing_inserts.inserted_by := v_username;
:auditing_inserts.item_inserted := v_item ;
:auditing_inserts.block_name := v_block;

END;

 

[lewisp]

There doesnt appear to be any code to initialise your V_ parameters. In both cases above the contents of each one will be NULL.

 

[egyhamada]

first i want to know
is
1. the variables you use what is its values
you must use the block items on the screen which have data
2. check if auditing_inserts is a database block

 

[sem]

I also can not see where you create a new record in auditing block. I may suppose that records are not inserted but rather the current record is updated each time

Regards, Dima

http://seminihin.narod.ru

 

[Apprentice101]

so sorry, I didnt post the complete code. please if you would, review again. I also got rid of the ITEM part.

DECLARE

v_block VARCHAR2(30);
v_username VARCHAR2(30);
v_date DATE;

BEGIN
v_username := GET_APPLICATION_PROPERTY(USERNAME);
v_date := SYSDATE;
v_block := :SYSTEM.CURSOR_BLOCK;

:auditing_inserts.inserted_date := v_date;
:auditing_inserts.inserted_by := v_username;
:auditing_inserts.block_name := v_block;

END;
*******************************************************
Also tried like this:

DECLARE

v_block VARCHAR2(30);
v_username VARCHAR2(30);
v_date DATE;

BEGIN
v_username := GET_APPLICATION_PROPERTY(USERNAME);
v_date := SYSDATE;
v_block := :SYSTEM.CURSOR_BLOCK;

COPY(v_date, v_block||'auditing_inserts.inserted_date');
COPY(v_username, v_block||'auditing_inserts.inserted_by');
COPY(v_item, 'auditing_inserts.item_inserted');
COPY(v_block,'auditing_inserts.block_name');

END;

 

[BJCooperIT]

Are you issuing a commit?

[sup]Beware of false knowledge; it is more dangerous than ignorance.[/sup][sup] ~George Bernard Shaw[/sup]
Sr. App. Developer Analyst/Custom Forms & PL/SQL - Oracle/Windows
Author and Sole Proprietor of: Emu Products Plus

 

[Apprentice101]

No commmit, I assumed that the commit gets processed as the record gets inserted doesnt?
more info,


the auditing_inserts is a block on my form and also a table on the database.



I also have a patient block which does receive the data from the form, this table gets the records inserted.

 

[lewisp]

These commands

[tt]COPY(v_date, v_block||'auditing_inserts.inserted_date');
COPY(v_username, v_block||'auditing_inserts.inserted_by');[/tt]

will get you nowhere because the result of the concatenation will be,for example, [tt]'auditing_insertsauditing_inserts.inserted_date'[/tt]

On which block have you placed the pre-insert trigger?

Does your auditing_inserts block have any records already queried and which record in that block is current?

 

[Apprentice101]

thanks lewisp,

I have place the pre-insert trigger at the form level.

the auditing_inserts block has no records at all, its an empty table so far and nothing is writting in it.

The intencion is to be able to track the changes on all the blocks by all the users. So ideally what I was trying to acomplish was this. if you have a better way to do this, please advice.

Table auditing_inserts

User | Date | Block

john 10/20/2005 patient
john 10/20/2005 treatment
john 10/20/2005 clinic


so my the idea is to know that john was able to insert records on all of those modules on that particular date.

I will also do it update and delete triggers.

my boss wants to know about each user's productivity.


thanks!

 

[lewisp]

Rather than have a separate block for the auditing, why dont you create pre-insert, pre-update and pre-delete triggers at the block level? Then in each do an explicit insert, update or delete:

INSERT INTO audit_table (
col1,
col2, ... )
VALUES (
:block.item1,
:block.item2, ... );

etc.

 

[Apprentice101]

well, I thought about that, but since I have like 35 blocks, I wanted to do it at the form level.

 

[lewisp]

Then use explicit DMLs in your form level trigger.

Incidentally, you will need to use SYSTEM.TRIGGER_BLOCK instead of CURSOR_BLOCK in your code.