0

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?

2

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.

  • I see. Trigger is just the first thing that comes to mind. Buy i can also resort to doing your suggestion. I have a question, can a stored proc handle this? Or it will just affect dbase performance? – user60216 Feb 22 '14 at 6:20
  • @user60216 - If the third party vendor can call stored procedures rather than issuing INSERT statements, yes, you could use stored procedures. – Justin Cave Feb 22 '14 at 6:25
  • Actually the INSERT statements are being done on our side. So it's possible to do it in store proc like, select the whole table into a temp table distinctly, add a field to count the duplicates, delete the duplicates, then insert the values from temp table to main table this time include the count when inserting values (i hope i explain it well. Lol!) – user60216 Feb 22 '14 at 6:32

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.