Implement a deduplication trigger in Oracle

Question

I am new in Oracle DB. We are using Toad for Oracle. I need to implement a deduplication trigger for our table. It's an alarms table and a third party vendor will be sending alarms to our database. Basically, it's a catch-all alarms. Duplicates are those with the same IP, alarm remarks and severity.

Sample data:

INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.10', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')
INSERT INTO t_alarms (IP, remarks, severity) VALUES ('10.10.10.19', 'VOICESERVER IS DOWN', '5')

And then my table looks like this:

IP          REMARKS               SEVERITY TALLY FIRSTOCCUR         LASTOCCUR
10.10.10.10 'VOICESERVER IS DOWN' 5        2     2/21/2014 01:00:03 2/22/2014 02:00:03
10.10.10.19 'VOICESERVER IS DOWN' 5        2     2/21/2014 06:00:03 2/22/2014 06:30:03 

where: Tally is the count of how many times my alarms happen FirstOccur is the date/time when it first inserted/happen LastOccur is the date/time when it last occur

Is there a way or a script to implement this dedupe process through triggers?

Answer 1

This is not the sort of task that you would want to use triggers on tables for.

If you really wanted to use a trigger, you could declare t_alarms as a view, rather than as a table. You could then write an INSTEAD OF trigger on the view that translated INSERT statements on the view into UPDATE statements against a new base table.

Were it me, though, I'd let the INSERT statements go into a table which allows duplicates and then create a materialized view on top of that table that does the aggregation. You can have that materialized view refresh ON COMMIT if you want to ensure that the data in the materialized view is always in sync with the data in the detail table.