cancel
Showing results for 
Search instead for 
Did you mean: 

Audit log table in sql server using PI

Former Member
0 Kudos

Hi Experts,

We have a requirement where the Interface is SAP -> PI -> JDBC.

1) The data from SAP tables has to be inserted to the table fields in DB. We have completed doing this.

2) But, added request is that, PI have to update a audit log table in DB  which has details like

Date, Time, action performed on the tables (INSERT/UPDATE) for each insert/update we do in the point 1).

Please let me know if this is possible and if so guide how to achieve the same.

Thanks,

Pavi

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member
0 Kudos

Hi Pavi,

If your database supports triggers, you can create triggers on the first table where the actual data is inserted.

CREATE TRIGGER Trigger_Name

   ON  First_Table_Name FOR <INSERT,DELETE,UPDATE>

You can the write the statements for updating the audit table in this trigger.

Regards,

Priya

Former Member
0 Kudos

Thanks for the reply.

Client does want triggers to be used. He wants this to happen through PI and not in DB thoush DB supports triggers.

iaki_vila
Active Contributor
0 Kudos

Hi Pavi,

If you want to audit when you first scenario is executed in a third DB table, you can do this at mapping level constructing an UDF for this

Can we insert data using jdbc lookup UDFin mess... | SCN

Also if you are in the same DB the first and the second scenario, you can do two inserts in the same call on the first scenario. With two statements tags in your jdbc SAP XML structure, may be this is the way that i like more.

Hope this helps.

Regards.

Message was edited by: Iñaki Vila

Former Member
0 Kudos

Thanks for the reply.

Can you please elaborate the second option. ( without UDF).

It is the same DB for both the scenarios.

iaki_vila
Active Contributor
0 Kudos

Hi Pavi,

If one PI request to the DB you can include more than one Insert statement.

Check design 2 in Raja Sekhar Reddy's blog:

In this way you can insert the main table and the audit log. However, in my opinion you can't know if the insert go well or not, in this case, if you need this information the best way is to have a stored procedure in DB in which SP the first part do the insert in the main table and later the insertion on the audit log, controlling all process directly.

Regards.