Update trigger

[benipal]

Hi
I want to capture the system date into 'modifydate' field in my 'employee' table whenever there is change in the value of the 'address' field of the 'employee' table.

Looking for a trigger for this task.
I am using Oracle 9i
Thank you

 

[SantaMufasa]

Benipal,

Tek-Tips suggests that we do not provide simply "coding services" in response to posts. We're happy to help you with code that you have produced. If you can post your attempt at creating such a trigger (after having consulted your Oracle Triggers references), even if it has errors or is non-functional, we'll be happy to offer suggestions to help complete the task.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[benipal]

create or replace trigger modifyemployee
after update of address on employee
begin
update employee set modifydate=sysdate

// i can't figure out the code to go over here

end;

thanks

 

[SantaMufasa]

Benipal,

Here is your trigger code. (Notice that you want a BEFORE UPDATE trigger so that it will include the new contents of "modifydate" in the newly updated row. Also, you want the trigger to fire "FOR EACH ROW", which allows you to access both the ":new." and "ld." values for each row that you are updating.):

create or replace trigger modifyemployee
    before update of address on employee
    for each row
begin
    :new.modifydate := sysdate;
end;
/

Trigger created.

SQL> update employee set address = 'xyz' where empnum = 10201;

1 row updated.

SQL> select address,modifydate from employee where empnum = 10201;

ADDRESS    MODIFYDAT
---------- ---------
xyz        29-OCT-09

1 row selected.

Let us know if this is what you wanted.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[benipal]

hi Mufasa
I am using an application to insert/update record in table.
The same SAVE button is used to create or update employee record.
When I change and save the address, it shows the updated address but modifydate field still NULL.
I see no error message while executing trigger.
Please advise,

 

[SantaMufasa]

Benipal,

You should be able to test the trigger by running an address UPDATE in SQL*Plus. Let us know those results.

Also, since your [Save] button affects both INSERT and UPDATE, then modify your trigger code to read:

before insert or update ...

Let us know the outcomes.

Mufasa
(aka Dave of Sandy, Utah, USA)
[I provide low-cost, remote Database Administration services: www.dasages.com]
“Beware of those that seek to protect you from harm or risk. The cost will be your freedoms and your liberty.”

 

[Beilstwh]

Also dont forget, the trigger will only fire if the ADDRESS is changed. If any other column is changed, then NO update will occur. Personally I would set the modify date when anything gets changed by using

create or replace trigger modifyemployee
before update or insert on employee
for each row
begin
:new.modifydate := sysdate;
end;
/

Bill
Lead Application Developer
New York State, USA

 

[benipal]

I have a test database which is a copy of the live database.
I am trying the following scripts on test database.

ALTER TABLE
EMPLOYEE
add (who_created varchar(30), when_created date );


ALTER TABLE
EMPLOYEE
modify (who_created default user, when_created default sysdate );


ALTER TABLE
EMPLOYEE
add (who_modified varchar(30) default null, when_modified date default null );


CREATE OR REPLACE TRIGGER modifyemployee
BEFORE UPDATE OF address ON employee
when (NEW.address != OLD.address)
FOR EACH ROW
BEGIN

:NEW.when_modified := sysdate;
:NEW.who_modified := user;

END;

When I run these scripts on test database, the trigger doesn't work.
I also get the error message in live database( related to when_modified field) even though I ran the scripts on test database.
Please advise
Thank you,

 

[Beilstwh]

Your column names are wrong. Use


CREATE OR REPLACE TRIGGER modifyemployee
BEFORE INSERT OR UPDATE OF address ON employee
when (NEW.address != NVL(OLD.address,'@@@'))
FOR EACH ROW
BEGIN

:NEW.when_created := sysdate;
:NEW.who_created := user;

END;

Bill
Lead Application Developer
New York State, USA

 

[benipal]

Hi Bill
Address is never NULL and I want to update the who_modified and when_modified fields with user and sysdate whenever there is change in address value.

 

[Beilstwh]

on an insert the old.address will always be null.

Bill
Lead Application Developer
New York State, USA