We have multiple tables which we need to retrieve data from and dump to one centralized table. Currently what we are doing is running an ETL job made from Pentaho, retrieve the records from the source tables, and dump it in the destination table, this runs every 5 mins. But now, we are required eliminate or at least lessen the interval between the running of the ETL job. One option is to lessen the interval time, another is to use triggers. But the only thing we are concerned with right now is the additional utilization cost of using triggers. I haven't much experience with triggers so I'm checking with you guys if triggers really do cost a lot of cpu/memory from the database server.
The cost of ETL should be cheaper because there's some level of batching involved, rather than doing work for every INSERT/UPDATE. But as the time requirement becomes smaller, trigger becomes more suitable answer.
On the other hand, if it can be implemented using trigger (e.g. copy to another table), maybe you don't really need to use ETL. You need ETL to do complex transformations and load the data to another system.
Triggers fire synchronously with the transaction which wrote the data initially. That Tx will incur further latency because of the trigger. This may be acceptable to you, or not. If the trigger code fails the original Tx will fail too.
Change Data Capture may be a reasonable approach. It can be asynchronous at runtime. The application will be ignorant of the existance of CDC. It has some system software dependencies and other overheads so check your installation can suppor these.
If significant manipulation of the data is required between source and destination (rather than a simple cut-an-past job) a proper ETL package run frequently is the way to go.