The ATC manual describes the process in detail (as do the Intelligent DB adapter docs) but here’s an overview of how the ATC and trigger mechanism work together. I’ll use an insert example.
-
An application performs an insert to a table.
-
A trigger is configured to fire after insert. Here’s an example trigger:
create or replace trigger MY_TRIGGER_AI
after insert on MY_TABLE
for each row
begin
insert into MY_TABLE_BUF
(business_object, component, level_num, operation, keyId, aw_rowid)
values
('OraObject', 'PaymentAdvice', 0, 'I', :new.KEY_ID, MY_TABLE_BUF_SEQ.nextval);
end;
-
The trigger writes a record to the buffer table. The keyId can be anything that uniquely identifies the row in the table. I’ve used single fields, concatenated fields, and ROWID (Oracle) successfully. The buffer table has the fields indicated in the trigger above.
-
The notifier is an adapter running on some machine, often on the broker server. It is configured to poll the buffer table every N seconds. It does a select on the buffer table. It copies all the records to a staging table. The staging table has the exact same structure as the buffer table.
-
Using configuration data from event definitions, the notifier creates an event for each row in the staging table and publishes them. Upon successful publish, the staging table is cleared. These notification events only have key data to identify the row in the database. An ATC will do a request/reply (select statement) via a database adapter to retrieve the data from the real table. (Intelligent database adpaters work a little bit differently.)
“Further the buffer tables have to be cleared immediatly after picking up data so avoid same data being fetched”
The buffer table will have an entry for each database activity. If a row is changed twice, then there will be two notifications fired. Depending on the changes made to the row, you may lose one of the changes (e.g. column B changed to ‘ABC’ and then to ‘XYZ’ right away before the adapter has picked up the change–both update notices will show column B as being ‘XYZ’). Often, duplicate updates such as this are not a problem. If your integration requires publishing each and every change, then you’ll need to devise a scheme to capture those–some sort of trigger and supplemental table.
“Suppose the data is picked up (select * ) buffer table is cleared and there is some network problem and data is lost.”
The staging table is not cleared until a successful publish to the broker. The data is safe in the database. The notifier and the broker used the publish sequence number to make sure no event is published twice.
“Polling the table is a better idea compared to triggers.”
I think you’ve already seen that for your case this is impossible unless you want to change your schema to add columns for insert date, update date and logically deleted flag. Triggers are an alternative to changing the schema and often the only way this can be done since db changes are oft times impractical/disallowed.
For deletes, a delete trigger can record the key fields of the deleted record (account number, part ID, sku, whatever) so that your event can inform other systems of the delete.
“if the inserts,updates and deletes are identified by adapter automatically as and when it happens(most important) then we can eliminate these triggers”
Fundamentally, triggers are really the only way databases allow you to capture these activities reliably. There is no magic way for an adapter, which is really just a client running SQL statements, to do any of the fun
#webMethods#Integration-Server-and-ESB#Universal-Messaging-Broker#broker