Trace deletes for a special table

[Former Member]

Hello Everybody

We have some strange problems with our Maxdb database (7.6.00.34) (no SAP System)

Every day we lose about 3000-4000 records in an table with 120000 records.

The table is filled with records from a sap system and all programmers are sure that their applications only read or update this table (never delete) ??????

Now i want to trace the database.

Is it possible to write a trace task, that only logs "deletes" on table xyz ??

If yes, how to do this ?

I have never used tracing before, so an example would be very helpfull.

Any help welcomed

Best Regards

Albert

[lbreddemann · Accepted solution]

Hi Albert,

In NetWeaver this would be pretty easy - but without, your application would have to provide a trace of the SQL calls it does.

On MaxDB level you cannot however turn on tracing for a specifc table/index or user.

You can just run precompiler/odbc/sqldbc-traces (depending on which client API your application uses) or you can activate the kernel trace (vtrace).

Neither option is quite effective for what your question is.

So one option might be - if your users have all their own db user to connect with - to create a trigger on that certain table that fires on DELETES and write the timestamp, the user and the primary key that got deleted into a logging table.

Anyhow: it would be a good idea to add a logging facility to your applications sql access code...

KR Lars

[Former Member · Accepted solution]

Hello Lars

Thank you for your quick answer.

All application users have their own dbusername.

The key of table stpersonalstamm is stpersonalstammid

I create a table psdeletelog with 3 fields

(user,recordkey,wann default timestamp)

Now i want to create a trigger(my first one

create trigger personalstammdelete for stpersonalstamm after delete execute

(insert into psdeletelog (user,recordkey) values(:xxx,:stpersonalstammid))

What is the current username ???

Is this syntax correct ???

Regards

Albert

[lbreddemann · Accepted solution]

Hi Albert,

the function user will return the current user.

Anyhow, it might be easier to just specify the default value for the username column as user.

Something like this:

create table DELLOG (
"TIMESTAMP" TIMESTAMP NOT NULL default TIMESTAMP,
"TABLEKEY" CHAR (50) NOT NULL,
"USERNAME" CHAR (32) NOT NULL default USER,
primary key ("TIMESTAMP", "TABLEKEY")
)

That way all you've to figure out yourself for the INSERT is the KEY of the deleted row.

For example if I've got a CUSTOMER table with ID as primary key the trigger could look like this:

create trigger logdelete for lars.customer after delete
execute (
   insert into lars.dellog values (default, :old.id, default);
)

TIMESTAMP and USERNAME will automaticall be filled by the database.

KR Lars

[Former Member · Accepted solution]

Hello Lars

Following your hints i create my logtable "PS_delete" with the defaults.

Then i tried to create the trigger:

create trigger logpstammdelete for stpersonalstamm after delete execute (

insert into ps_delete (uhrzeit,benutzer,stpersonalstammid) values (default, default, :old.stpersonalstammid)

)

Error -5015

missing keyword: WITH

??????

Regards

Albert

[lbreddemann · Accepted solution]

Hi Albert,

please specify the full schema-name for tablereferences in the trigger and don't forget the ';' after the insert statement.

create trigger logpstammdelete for schema.stpersonalstamm

after delete execute (

insert into schema.ps_delete (uhrzeit,benutzer,stpersonalstammid)

values (default, default, :old.stpersonalstammid);

)

KR Lars

[Former Member · Accepted solution]

Hello Lars

Thank you very much.

Regards

Albert