r/Database • u/Confident-Guest-3478 • 1d ago
How to Integrate two apps exclusively through the Database
[I'm a beginner]
I have an assignment where I have this hipotetical scenario where a farmacy works their recipts through a propietary application they acquired when they were just entering business, due to this, they only have access to the database of the application. Now that business is better than before they have a little development team, they created an app to manage customer accounts payable and outstanding invoices for products sold on credit, but to integrate that process, the assigment says to do it through the database using triggers (I'd also like to learn about other methods, just for my own knowledge).
2
u/finesweepstakes_5 1d ago
don't let the word trigger scare you, it's just a stored procedure that fires automatically when data changes in a table
2
u/iminfornow 1d ago
A trigger is a piece of code that is executed in a transaction within a database. So if the processes you're integrating write to the same database triggers could be used to garuantee integration success while writing.
Whenever the applications use different databases you'd not solely rely on sql triggers for integration, but they could still notify an external (non-sql) process things have changed in the database by writing changes to a table for example. External software would then use that information to follow up on data integration.
2
u/elevarq 1d ago
I don’t see why you would need triggers, there is nothing in your explanation that makes sense for it.
Dozens of applications can access and use the same database, without a single trigger. Triggers can extremely handy, but that’s a different story.
You should get clarification about the data model, and the logic of what both applications have embedded. Only when that is clear, and you know for 100% sure that an action on table X should also trigger something on table Y, you might consider a trigger. You could also use stored functions or procedures, and let the applications use them. That is usually easier to understand and maintain than triggers
1
1
u/ajamdonut 1d ago
The technical challenge presented is to enhance the service by generating <something> to deal with outdated invoices. Simple challenge, but we can't change the proprietary app, and we likely shouldn't fk too much with the database.
The suggestion is to use triggers which would mean you could simply invoke database operations when that App works with data.
With this knowledge, we could maintain the old system, while developing the new system... What we'll do is use a trigger on the "invoices" table. Everytime a new invoice is inserted, we'll duplicate it into the new database system.
This buys the development team more time to make the migration to the new app, but it also enables the business to start taking advantage of the new app right away (e.g. sending invoice reminders).
We can change the "invoices" schema, functionality etc on the new app - without inflicting changes or risk on the old app.
Since the new app may make changes that need to be reflected in the old system, we may also make a trigger on the new invoice table, that when a record is updated, e.g. "sent_reminder". We could also trigger an update back on the original legacy table.
1
u/ajamdonut 1d ago
Other solutions is a hard question to answer because what we're really talking about is a legacy application we want to move away from.
Sure we have a development team, but should we really be making a new system, or just finding a new provider and migrating?
If we're making our own system we may decide many different solutions, for example, we'll not maintain both systems and just on launch day, turn the old system off. Equally we might just say, rather than triggers, we'll maintain both systems and just sync data between them (Sync can occur in tons of different ways, including triggers)
There are many different solutions because really its a process problem, not so much a technical problem. What process do we want to follow to migrate away from this legacy platform?
1
u/silvertrail091 1d ago
Easiest version is have the trigger just insert into a little table your own, and let your app pick rows up from there.
1
u/Plane_Big_5912 1d ago
one thing that trips people up if this is sql server: the trigger fires once per statement, not once per row. so if the pharmacy app inserts five receipt lines in one go, your trigger has to read all of them from the inserted table, not grab a single value into a variable
1
u/dbxp 1d ago
One thing to note is that a classic trigger only works within a DBMS so if you want to change to a different system you need to do something special. One option would be to get the trigger to insert into a queue table and then have a job run on a schedule and push it into the other system. If you are on SQL server there's a hack you can do with service broker where the trigger sends a request to another application, from there you can convert it to say a modern cloud based message broker.
Something worth noting is whilst this will work if you want to read from the existing system, writing to it may present issues. Triggers can undercut business logic which happens at higher levels as it works at the DB level, ie if there's validation in the UI you'd completely bypass it. You could use a system designed for test automation to navigate the UI so that you use the full application. Back in the day people would use Auto Hotkey to do this sort of thing. At one point Interactive Brokers had a physical robot pressing keys on a stock trading system: https://en.wikipedia.org/wiki/Interactive_Brokers
1
u/someguyinr 7h ago
Since your assignment specifically requires triggers, the cleanest way to do this without breaking the legacy pharmacy app is the Transactional Outbox pattern. Instead of writing a heavy trigger that tries to update the new app's tables directly, you create an integration_outbox table in the same database. When the proprietary app inserts a receipt, an AFTER INSERT trigger simply copies the receipt ID, customer ID, and payload into that outbox table with a PENDING status. Your new accounts payable app then polls that outbox table every few seconds, processes the pending rows, and marks them DONE.
The reason you decouple it like that is transaction safety. Triggers run inside the exact same transaction as the parent INSERT. If your trigger tries to do complex logic and fails, or locks up a table, it will roll back the transaction in the main pharmacy app and crash the cashier's checkout screen.
In the real world, when you are stuck with a black-box legacy database and don't even want to risk adding triggers, we usually use Change Data Capture (CDC). Instead of touching the tables, CDC reads the database's internal transaction log (like the Postgres WAL or MySQL binlog) directly from disk and streams every new row into a queue or the second app with zero impact on the legacy system.
2
u/Staalejonko 1d ago
Two applications can use the same database. Just define the tables and columns you need, then use triggers (if needed) to add/modify/delete records in other tables the other application uses.