Discussions
Categories
- 385.5K All Categories
- 5.1K Data
- 2.5K Big Data Appliance
- 2.5K Data Science
- 453.4K Databases
- 223.2K General Database Discussions
- 3.8K Java and JavaScript in the Database
- 47 Multilingual Engine
- 606 MySQL Community Space
- 486 NoSQL Database
- 7.9K Oracle Database Express Edition (XE)
- 3.2K ORDS, SODA & JSON in the Database
- 585 SQLcl
- 4K SQL Developer Data Modeler
- 188K SQL & PL/SQL
- 21.5K SQL Developer
- 46 Data Integration
- 46 GoldenGate
- 298.4K Development
- 4 Application Development
- 20 Developer Projects
- 166 Programming Languages
- 295K Development Tools
- 150 DevOps
- 3.1K QA/Testing
- 646.7K Java
- 37 Java Learning Subscription
- 37.1K Database Connectivity
- 201 Java Community Process
- 108 Java 25
- 22.2K Java APIs
- 138.3K Java Development Tools
- 165.4K Java EE (Java Enterprise Edition)
- 22 Java Essentials
- 176 Java 8 Questions
- 86K Java Programming
- 82 Java Puzzle Ball
- 65.1K New To Java
- 1.7K Training / Learning / Certification
- 13.8K Java HotSpot Virtual Machine
- 94.3K Java SE
- 13.8K Java Security
- 208 Java User Groups
- 25 JavaScript - Nashorn
- Programs
- 667 LiveLabs
- 41 Workshops
- 10.3K Software
- 6.7K Berkeley DB Family
- 3.6K JHeadstart
- 6K Other Languages
- 2.3K Chinese
- 207 Deutsche Oracle Community
- 1.1K Español
- 1.9K Japanese
- 474 Portuguese
understanding triggers to store old db values

716107
Member Posts: 84
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!!!
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!!!
Answers
-
Hoek Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold CrownIf 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 -
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 Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold CrownI got an oracle errorIf 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?
-
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 Senior Developer Den Haag, Nederland | The Hague, The NetherlandsMember Posts: 16,089 Gold CrownI 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 -
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... -
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 -
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? -
user11150264 wrote: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.
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?
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 -
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.