understanding triggers to store old db values

716107

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!!!

Hoek

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

716107

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

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

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

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

716107

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

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

716107

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

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

716107

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;