Trigger on Z* table

[FredericGirod]

Hello,

I have a Z* table, which is update by an external program (not SAP). I need to know each time this table have a modif or an update.

Regards

Frédéric

[andreas_mann3]

Hi Frédéric ,

some possible solutions:

1) TA SCDO -> change documents (tables cdhdr /cdpos)

2) append your z-table with field "udate" and

update this field by every run of ext. program

regards Andreas

[Former Member]

Hi Frédéric ,

In the technical Settings of the table, there's an option called "Log Data Changes". Please consider this option. You could start off with the F1 help for this checkbox.

Regards,

Anand Mandalika.

[Former Member]

Hi everyone,

I'm afraid I disagree that checking the flag "Log data changes" works with this issue, for the data is updated outside SAP. By checking this flag, every time a table is update within SAP, then tables CDHDR & CDPOS are populated.

My suggestion is: create a trigger in the database where SAP lies, that is, *outside* SAP. It will work for sure. You know,

create or replace trigger sapr3.your_trigger after update
or insert or delete on sapr3.z* for each row
(here your pl/sql)

(this is Oracle-syntax)

Please let us know if it helped. BR,

Alvaro

Message was edited by: Alvaro Vidal-Abarca

[Former Member]

Hello Alvaro,

I think Checking this option would do the logging if the other conditions are satisfied. I don't think CDHDR and CDPOS have anything to do here.

You might be confused with another similar flag which you find for data-elements - <i>Change Document.</i>

Let me know what you think.

Regards,

Anand Mandalika.

[Former Member]

Oops, you're right!

Sorry for the misunderstanding...

What I meant is that flag "Log data changes" only works if the table is updated via SAP.

[Former Member]

Hi Alvaro,

I am just curious to know, how, if you have a trigger at the database level, a program in SAP will be executed when an entry is inserted/update/deleted in the Z table?

I see that the question is answered, but I would appreciate if the solution can be shared here.

Thanks,

Srinivas

[Former Member]

Hi Srinivas,

well, not really. If you create a trigger, you can execute a piece of PL/SQL code every time an entry is inserted/updated/deleted. This PL/SQL could perform any operations on the DB where SAP is, or even on DB's outside SAP (via DB-links).

In order to run a program in SAP, (sigh) I really don't know. Maybe you could call a Java class into this PL/SQL, and this Java class calls an OS-SAP-program (maybe "sapexec") that runs an ABAP-program.

Phew!!!! That's a trigger that calls a PL/SQL that calls a Java class that calls an OS-program that calls an ABAP. I need a rest

Anyone can help guys?

BR,

Alvaro

[Former Member]

Hi Frederic,

Here is another alternative. I am assuming that this is a table defined in SAP's data dictionary and this external program is updating the data through SAP's RFC mechanism. Also, I am assuming that you have a table maintenance generated for this table. If my assumption is correct, here is what I think will work.

Use SE54 to create event for the table. The best event for you would be '02--> After saving the data in the database'. For this event you attach your code to trigger the other program which needs to know if any entry is made/changed/deleted in this table. I haven't tried calling another program from within the code of an event, but you can also try calling a function module to submit your program. Alternatively, you can raise a background event(not to be confused with the SE54 event) and scehdule a job to run your program when this event is raised. You can define this background event in SM62 and raise it using the function module BP_EVENT_RAISE.

If this external program is updating the table at a underlying database level, then you have to work with database triggers, but I am not sure how you will let SAP program know that a trigger has been raised at the database level.

Regards,

Srinivas

[Former Member]

Hi Srinivas,

I just have a small doubt here. If indeed a custom RFC Function Module is being used to update the table and we have access to change that FM, why do we still need to have the event mechanism ? Depending on the success of the MODIFY/UPDATE statement, the required process can be triggerred directly, isn't it ?

Regards,

Anand Mandalika.

[FredericGirod]

Thanks all for your answers,

The table Z* is update by an external program (not in SAP system) and the program don't use a RFC function.

[Former Member]

Hello Frédéric,

How does the external program update the table without the RFC ? Do you mean that it accesses the database directly (without going through the Application Server) and updates it ? In that case, the solution suggested by Alvaro might have to be considered.

Regards,

Anand Mandalika.

[Former Member]

You are correct Anand. I was not thinking. When I said an RFC, I was referring to just remote execution of the external program that updates the table, not the RFC function module that has access to the data. So if I call an external program 'abc.exe' that does a direct update to the database table(not through the SAP's application layer), then I think the events won't be of any use.

But I think if we define a external OS command using SM69, and then execute it within an RFC using the FM 'SXPG_COMMAND_EXECUTE'. Then based on the return status (assuming the external program returns status), the SAP program can continue with its process of reading the new/updated records. You still need the extra fields for date/time updated/inserted though.

Srinivas

[former_member182046]

Hi,

to clarify some points on CDHDR/CDPOS:

Tables CDPOS and CDHDR are populated by function modules *_WRITE_DOCUMENT. These function modules are generated from so-called Change Documents Objects, which are maintained in transaction SCDO.

Most SAP applications call these function modules when updating standard tables. If you add Z* tables to an application, you have two choices:

1) Extension of an existing business object

Add your table to an existing change documents object in transaction SCDO. When updating your table, call ..._WRITE_DOCUMENT in the same LUW.

2) New business object

Create a new change document object in transaction SCDO. When updating your table, call ..._WRITE_DOCUMENT in the same LUW.

The ..._WRITE_DOCUMENT are called for each changed table record. They receive a parameter containing the old state and another parameter containing the new state of the record. Both are compared and the differences are written into tables CDHDR and CDPOS as one change document. CDHDR contains the header of the change document. CDPOS contains one or more positions (changed field values) of the change document.

Only changes to fields whose data elements are marked as relevant for the creation of change documents in SE11 lead to the creation of change documents. That is, if you call ..._WRITE_DOCUMENT with two identical records for old and new, no change document will be written. If the old and new record are different, but only in fields whose data elements do not have the "relevant for the creation of change documents" checkbox marked, no change documents will be written.

Best regards,

Thorsten

[FredericGirod]

Hi Thorsten,

I have start to solve my problem with your solution :

-> WE81 create a message type, BD50 activate, SCDO create table entrie and BD52. (don't find how create function)

But .. I stop cause I don't know where my function will be used. If I have right understand, I must use my function with a program I create to populate the table ? Or does this function will be call each time something change in the table ?

Your explanation is great, but I never find a real doc about that.

Thanks again

Frédéric

[former_member182046]

Hi Frederic,

the function module has to be called each time records in the table are changed. The program that changes the table must also call ..._WRITE_DOCUMENT for the related change document object. It's good practise to do this in the same LUW so that if your update transaction is rolled back (for example due to a short dump), any change documents about it will suffer the same fate.

Anyway, I found some online documentation that should help you find your way around change documents:

http://help.sap.com/saphelp_nw04/helpdata/en/2a/fa015b493111d182b70000e829fbfe/frameset.htm

Best regards,

Thorsten

[moshe_elimelech]

Dear Thorsten ,

You wrote :

Only changes to fields whose data elements are marked as relevant for the creation of change documents in SE11 lead to the creation of change documents.

I can't find in SE11 this field , can you add a hint .

Thanks ,

Amnon