Best way to create a database Trigger

Best way to create a database Trigger

  
Hello,

I'm trying to run a timer (run on publish) to create some triggers for some tables in the SQL Server database.
I'm getting permissions error because it is running with OSRuntime.
The user that have access to create trigers is OSAdmin.

What is the best way to solve the problem of triggers creation ?

Actually I did not want to give permission for it because this OSRuntime creation. I only need to create them in the publication of the eSpace.
vilnena -

That is a really bad idea. Not just because of permissions at the DB level, but all around. Yes, you can dig through the Entity entity in System to get physical table names, and look in other entities to get the rest of the information you are looking for, but the idea is a train wreck in reality.

A much better approach is to use the Processes system, and have a process triggered by the OnCreate action of the entity you are interested in. Even better than that, is to design your application so that the entities are in a separare eSpace and marked as Public = Yes and Expose Read Only = Yes, and build a proper data access layer that controls the C-UD operations. That way, you can safely control everything within OutSystems, and not have to deal with the hassle of trying to build SQL inside of the system. And, if you want a Web Service layer too, it's just a few clicks to expose that data access layer as Web Services.

J.Ja
Justin James wrote:
vilnena -

That is a really bad idea. Not just because of permissions at the DB level, but all around. Yes, you can dig through the Entity entity in System to get physical table names, and look in other entities to get the rest of the information you are looking for, but the idea is a train wreck in reality.

A much better approach is to use the Processes system, and have a process triggered by the OnCreate action of the entity you are interested in. Even better than that, is to design your application so that the entities are in a separare eSpace and marked as Public = Yes and Expose Read Only = Yes, and build a proper data access layer that controls the C-UD operations. That way, you can safely control everything within OutSystems, and not have to deal with the hassle of trying to build SQL inside of the system. And, if you want a Web Service layer too, it's just a few clicks to expose that data access layer as Web Services.

J.Ja

 Hi James,

Thank's for your reply. Im deed i'm aware of the architectural problems (i full agree with your approach), but there are some thinks that the development platform should deal without the explicit coding of the developer, this triggers will be used for audit some information saved on other tables/catalog.

Is there a option to create the triggers?

vilhena -

No, there is no option built in to it. That said, one of the nice things about this system is that you don't involve DBAs in the development process for the most part. If someone there is insisting that you do things like stored procedures, triggers, views, DB functions, etc., then you are not going to be happy, or they are not going to be happy. I see these things as red flags (another one is using the Advanced SQL) that say, "you need to examine how you use this".

J.Ja