First of all, I am not a trained DBA, so I am not sure if I am even doing this right. That being said, I have to do it.
I am trying to create a trigger to monitor when something is deleted from the database, and have it insert in a table what was deleted and give a timestamp and date...
create or replace
"TRIGGERACCESSPOINT" AFTER DELETE ON SECOMORA.ACCESS_POINT@ospi
INSERT INTO DELITM_TABLE (NAME, WHEN) VALUES ('Access Point');
this is what I have so far. though I am getting errors. ORA-00947: not enough values, I think I understand that it means since I have two columns that I need another value. I am just not sure what to put there so that I can get the time and date the event occurred.
Thanks in advance for any help
02-01-2013, 03:14 PM
"INSERT INTO DELITM_TABLE (NAME, WHEN) VALUES ('Access Point');"
You are telling Oracle that you are inserting two columns, but you are only providing one value. Here is an example of how to make the insert statement work.
"INSERT INTO DELITM_TABLE (NAME, WHEN) VALUES ('Access Point', SYSDATE);"
SYSDATE is a function that returns the current time.
02-01-2013, 04:30 PM
Thank you so much, that makes sense now. And it works, now to duplicate for all the other tables..
02-01-2013, 05:16 PM
But what does it tell you? It is a statement level insert that tells you that someone deleted something from a table.
That doesn't seem very useful. If you want to audit deletes, then you should look at Oracle auditing.
02-01-2013, 05:33 PM
Basically we have a mapping software that stores its information on the oracle database, and at the moment we only have one person doing the editing of the maps. But we have had things in the mapping software disappear so the company who made the mapping software told me to put some triggers in to monitor deletes. So with the one above and two others, I am hoping it will show me if the items are being deleted from the database or if there is something in the mapping software causing the issue. If that makes sense.
02-02-2013, 10:18 AM
You probably still want to turn on auditing. But to find out why rows are disappearing, if you use PL/SQL to insert data, check to see if you also have the following in your code:
WHEN OTHERS THEN NULL;
I would guess that you have buggy code, and having code that hides error messages doesn't help.