DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: triggers

  1. #1
    Join Date
    Feb 2013
    Posts
    3

    triggers

    Hi,

    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
    TRIGGER
    "TRIGGERACCESSPOINT" AFTER DELETE ON SECOMORA.ACCESS_POINT@ospi
    BEGIN
    INSERT INTO DELITM_TABLE (NAME, WHEN) VALUES ('Access Point');
    END;

    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
    Ryvur

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    "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.

  3. #3
    Join Date
    Feb 2013
    Posts
    3
    gandolf989,

    Thank you so much, that makes sense now. And it works, now to duplicate for all the other tables..

    Ryvur

  4. #4
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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.

  5. #5
    Join Date
    Feb 2013
    Posts
    3
    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.

    Ryvur

  6. #6
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,166
    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:

    EXCEPTION
    WHEN OTHERS THEN NULL;
    END;
    /

    I would guess that you have buggy code, and having code that hides error messages doesn't help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width