João Pinto (Customer) asked a question.
Define trigger for all tables
Hi there!
I wanted to ask if there's a way to create a trigger for all tables ("write" for example).
I was thinking on using the following code in a persistant procedure:
- ON WRITE OF Customer DO:
-
- END.
But I don't want to have to have a block of code for each table. Is there a way to do the previous code dynamically? If not, what would be my best option?
Thank you!
JP
Why do you need to do this? What are you actually trying to accomplish?
I want to create a record when ANY table is updated... Instead of changing and creating triggers I wanted to detect it in a persistante procedure (which i can). The problem is that i don't know how to do it dynamically... The other option would be creating a block of code for each table (which are more than 300).
Sounds like what you are trying to do is already built.
Is this for auditing? if so, then use the database auditing features. Are you planning on replicating the changes out to somewhere else? If so, the look into using Pro2. If you want to build it yourself, you can use enable change data capture.
The record i want to create is a kind of log. Something that will alow to track everything a user does but only developers have access to it. For example: I update table A. The trigger "write" of table A will create record on table x, saving all information necessary to track and revert this activity any time i want. I don't want to export it or anything like that. I just for interval/development usage). This would be added to more than 130 clients. I needed a fast, simple and low-time-consuming solution...
Sounds like CDC (change data capture) is inline with what you are looking for. It is policy based, and can be enabled online. It captures all changes written for the tables (with a configurable fields list)) you designate and writes them to change capture tables where it can be read back out from ABL.
hey Matt. I was trying to use "CDC" but I most likely doing something wrong... I followed the steps on this link and i updated a table on my database and it didn't create any record on the '_Cdc-Change-Tracking' table. I even tried to re-follow the stops but i get the "Change Data Capture Feature already enabled for database x(17470)" message. Think you can help me?
Enabling CDC is only a first step. That creates the initial set of tables needed. You then need to create the CDC policies to control what gets captured.
Start here for the overview:
https://docs.progress.com/bundle/openedge-database-change-data-capture/page/Change-Data-Capture-Overview.html
This page starts the section for policies. There is some design considerations depending on exactly what you need.
https://docs.progress.com/bundle/openedge-database-change-data-capture/page/CDC-Policies.html
This is specific instructions on how to use the openedge explorer user interface to create policies:
https://docs.progress.com/bundle/openedge-management-change-data-capture/page/Creating-a-Change-Data-Capture-policy.html
And some videos on youtube:
https://www.youtube.com/watch?v=439Byi-TiGI
https://www.youtube.com/watch?v=BIvcXuHN6Fw
If you want to script the creation of the policies here is a video that talks about using the built-in API to create the policies:
https://www.progress.com/video/creating-a-new-cdc-policy-using-the-abl-api-for-change-data-capture
Do i need to create a policie to each table?
yes