Forum Stats

  • 3,852,382 Users
  • 2,264,100 Discussions
  • 7,905,056 Comments

Discussions

understanding triggers to store old db values

716107
716107 Member Posts: 84
edited Oct 26, 2010 5:48PM in SQL & PL/SQL
Hi Everyone,

I'm new to triggers and am just trying to understand how to make them work for what I need to do...
Basically I have an application with an associated oracle 10g database. One of the database tables contains information relating to people, and is aptly named 'people'. A single email is stored for each person. When an email address is updated the previous email information is lost. I need to be able to keep this info!

As I have no control over the application, only the database, I'd like to store old email addresses using oracle triggers.
Something like:

CREATE OR REPLACE TRIGGER email_address_log
AFTER UPDATE ON people.email
FOR EACH ROW
WHEN (old.email <> new.email)
BEGIN
old_email_var = old.email
INSERT INTO old_email_addresses (person_id, old_email, date_modified)
VALUES (person_id, old_email_var, SYSDATE);
END;


Although I don't really understand how the trigger gets the person_id value... nor do I understand why this won't compile if I leave out the person id value...
If there's a good tutorial anywhere it would be great if you could point me in the right direction!!!

Thanks!!!
Tagged:

Answers

  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    edited Oct 24, 2010 5:18PM
    If there's a good tutorial anywhere it would be great if you could point me in the right direction!!!
    Documentation:
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#ABC1032282

    Or do some searches on asktom:
    http://asktom.oracle.com/pls/apex/f?p=100:11:0::::P11_QUESTION_ID:745878232038
    or oracle-base:
    http://www.oracle-base.com/articles/10g/Auditing_10gR2.php

    Anyway, you'll have to use colons ( the ':') when working with triggers, something like this:
    create or replace trigger email_address_log
     after update on people.email
     for each row
     when (old.email != new.email)
    begin
     insert into old_email_addresses(person_id, old_email, date_modified) 
     values(:old.person_id, :old.email, sysdate);
    end;
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1148
    Hoek
  • 716107
    716107 Member Posts: 84
    Thanks hoek... I'll take a look at the links you provided now.
    I did have the ':' in the first instance and I got an oracle error that basically said I couldn't use the ':' and it was recommended that I just change to the above version...
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    I got an oracle error
    If you get an error, always post it. The full error code and message. As much as possible.
    it was recommended that I just change to the above version...
    Who/what recommended that?
  • 716107
    716107 Member Posts: 84
    Ok.. So you're suggesting that I need to use auditing here... Something like:


    CREATE OR REPLACE TRIGGER email_address_log
    AFTER INSERT OR DELETE OR UPDATE ON people
    FOR EACH ROW
    WHEN (:old.email <> :new.email)
    BEGIN
    IF Auditpackage.Reason IS NULL THEN
    EXECUTE AUDITPACKAGE.SET_REASON(reason_string)
    END IF;
    INSERT INTO old_email_addresses VALUES (:old.person_id, :new.email, Sysdate );
    AUDITPACKAGE.SET_REASON(NULL);
    END;


    Btw can I do after update on 'people.email' or does it have to be just 'people'? I only want this to run when the email address is updated, not anything else in the table... I guess this is covered by the when clause?

    I guess I'll have to investigate setting the auditpackage up now!
  • Hoek
    Hoek Member Posts: 16,087 Gold Crown
    I guess this is covered by the when clause?
    That's whats the WHEN clause is for, yes.
    http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14251/adfns_triggers.htm#sthref1208
    Hoek
  • 716107
    716107 Member Posts: 84
    Do you have any resources on how to find the reason_string value?

    EXECUTE AUDITPACKAGE.SET_REASON(reason_string)

    I'm having a bit of trouble working it out...
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    AuditPackage is not something Oracle provides. If it exists on your system, that implies that is something that someone in your organization has written. You'd have to find someone in your organization that understands what sort of reason they would expect you to pass in.

    Justin
    JustinCave
  • 716107
    716107 Member Posts: 84
    I'm so confused...
    So you can't download auditpackage, you have to code it yourself?

    And this trigger can not be achieved without it?
  • JustinCave
    JustinCave Member Posts: 30,293 Gold Crown
    user11150264 wrote:
    I'm so confused...
    So you can't download auditpackage, you have to code it yourself?

    And this trigger can not be achieved without it?
    I'm not sure where the calls to the AuditPackage came from. No one else has suggested those calls. I was assuming that someone in your organization had coded a package and suggested that you call it-- presumably because it is doing something that integrates your code better with other audit triggers in your system.

    If AuditPackage doesn't exist in your system, there is no need to create it-- you can just remove the references to the package from your trigger.

    Justin
    JustinCave
  • 716107
    716107 Member Posts: 84
    Ok.. thanks everyone... the auditpackage came from one of the links hoek suggested.

    This final code was successful:

    CREATE OR REPLACE TRIGGER email_address_log
    AFTER INSERT OR DELETE OR UPDATE ON people
    FOR EACH ROW
    WHEN (old.email <> new.email)
    BEGIN
    INSERT INTO old_email_addresses VALUES (:old.person_id, :old.email, Sysdate );
    END;
This discussion has been closed.