Launch stored procedure and triggers

[Former Member]

Hello Experts,

I am having some questions about software architecture related to triggers and stored procedure.

In SQL server, you can execute a stored procedure from a Trigger.

It seams that this is not possible in SAP HANA.

First questions are:

Can you really not execute procedure from a trigger ?

Does someone knows why is this not possible ? Is there an improvement scheduled ?

Is there the equivalent of the SQL Server Message Broker on SAP HANA ?

Second question is :

I would like to execute my procedure automatically at start (when hana starts) or via scheduling.

But then, we have to choose between two problems :

• If launched at start this means that my procedure will be using a "do while" + a pause
  • Does someone knows how to do that ? (the launch at start ; not the do while and pause)
• If launched through an external scheduler, I must control that the external scheduler is not down or will not crash, I have to ensure that I won't launch two procedures at the same time etc ...
  • I know can do that with HDBSQL but this raise a lot of issues.

Has someone an idea that would allow the database engine to handle this kind of stuff by itself without having to think about a solution "out of the box" ?

Thanks in advance for any answer.

Regards,

Matthieu

[lbreddemann · Accepted solution]

Did you look into the built-in scheduler? It's part of the XSEngine of SAP HANA.

Hooking on procedure calls to table triggers is IMHO a terrible bug in most situations.

Not having that feature definitively prevents a lot of badly built systems and I am happy to never see such a feature in SAP HANA:

Really, one doesn't want action based on a table change. Instead what one wants is action based on information change, which requires a higher level mechanism.

- Lars

[Former Member · Accepted solution]

Hello Lars,

Thank you for your answer. We will have a look at the built-in scheduler.

As for badly built systems, it always depend on what you wanna do...

The trigger is positioned on a table that does store information change : it stores the last update date of whole lot of insert or updates made by one end-user in another table.

The purpose of it is to add a custom feature by cleverly using the database engine.

That's another subject, but I prefer a system that allows you to actually do something than preventing you from doing it. If it's dumb ... well, computers are only doing what they were asked to do

Matthieu

[lbreddemann · Accepted solution]

I see your point but I concur.

A DBMS really shouldn't just provide all features or feature

combinations for which there are edge conditions that may make sense eventually.

Instead, todays DBMS really are data processing platforms that provide services.

And I rather have a platform/API/framework that does the things it does very well, than one that has a million features but most of them only apply in one or two cases.

You seem to be designing a information life-cycle management function and something like that typically hinges into the actual application (as the life-cycle of an information only makes sense in its very context of application).

Well, let us know how you go with your system and how the trigger approach (in case you go with it) works in a years time.

- Lars