Creating new sequence in Oracle

[Former Member]

Hi,

Can anyone tell me how to create a sequence in Oracle for creating triggers. We are planning to create trigger in Oracle for any change in specific SAP database table to update a z-table. Can anyone please give a step-by-step approach for this.

Thanks & Regards,

Raj

[lbreddemann · Accepted solution]

> We are planning to create trigger in Oracle for any change in specific SAP database table to update a z-table. Can anyone please give a step-by-step approach for this.

Hi there,

exactly this kind of usage of triggers is not supported by SAP as of note

#105047 - Support for Oracle functions in the SAP environment, Pnt 58.

Besides the usual issues of triggers (they are not obvious... hidden... do something in the background without letting the user know) you may get into problems, as soon as something does not work anymore and support asks you to disable the trigger.

A better approach to the issue (one that also wouldn't require some obscure database level implementation) would be to use ABAP/J2EE layer functionality e.g. extension points to replicate the data.

Depending on what you actually want to achieve a view might do as well (or even a materialized view).

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

Thanks for warning me of the issues on this approach. But my issue is to extract the delta of some transaction tables which doesnt have date/time stamp. And it was given in some posts that this approach is used successfully and so thought of checking that approach.

Is there an alteranate approach that you can suggest to handle this situation. (we actually have to extract delta of many tables).

Thanks & Regards,

Raj

[lbreddemann · Accepted solution]

> Is there an alteranate approach that you can suggest to handle this situation. (we actually have to extract delta of many tables).

Hmm... what should be done with these delta?

Maybe table logging already does what you want to achieve!?

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

Thank for replying. But from some findings we got to know that there will be some performance issues with Table Logs. Moreover, there needs to be different approaches for DateTimeStamp tables, tables with CDHDR entries and for others to activate the change log indicator in table technical settings and proceed further.

Instead, we are looking for a generic and unique approach to extract delta from all types of table. That is the reason we thought of Oracle Triggers which we belive should be fine if the Oracle code is written correctly ( which obviously should be). (Seen some posts where people has used this alternate method and working fine). Please let us know what known issues are there with this approach.

Any help will be greatly appreciable.

Thanks & Regards,

Raj.

[lbreddemann · Accepted solution]

>Please let us know what known issues are there with this approach.

You mean apart from the fact that it's not supported and that "it should be written correctly" is basically a kind of guarantee for failure?

Think about what happens to your delta loading when the base tables are altered, say because some notes are applied or a new patch is installed?

How do you adapt your "unique and generic" solution to that?

How do you guarantee that these triggers won't cause lock-situations across multiple, relatively independent transactions?

How do you do the transport management to the other systems? Do you plan to manually apply the trigger implementation to all of the databases?

Would be interesting to learn what the compliance auditors have to say about this...

I don't know what other posts you refer to where such a solution works fine.

But I do know that they run a unsupported setup and the only solution they will get from support for any kind of problem related to this will be: turn off the triggers.

But possibly, due to the fact that barely anyone ever thinks of triggers when handling support messages (you know, that's because they aren't supposed to be there, because they're not supported !), it may be that a support message takes ages until somebody eventually figures out the reason.

So, well, for me this is a long and terrible list of "known issues".

regards,

Lars

[Former Member · Accepted solution]

Hi Lars,

Thank you for giving the elaborative list of issues we can expect due to the oracle trigger approach. Thank you for taking pain to guide in not to take an unsupported path. We actually have found some posts and even white papers explaining this approach and so thought of the approach. But since SAP is not supporting this methodology we dropped that idea but also wanted to know other issues with this method. Now we are searching for a new approach.

Thanks & Regards,

Raj