Home » RDBMS Server » Security » Urgent - forgotten - Trigger code for audit columns
Urgent - forgotten - Trigger code for audit columns [message #52780] Tue, 13 August 2002 05:16 Go to next message
Swamy
Messages: 78
Registered: June 2002
Member
I have a table with several columns + audit columns like INSERTED_BY, INSERT_DATE, UPDATED_BY, UPDATE_DATE. Whenever, this table is inserted or updated, I need to fill the audit columns via a database trigger. I need the body of the trigger. Please help me.

CREATE OR REPLACE TRIGGER IC_BIUR
BEFORE INSERT OR UPDATE OF LAST_NAME, FIRST_NAME
ON INSURANCE_CONTACTS
FOR EACH ROW
-- PL/SQL Block
begin
IF INSERTING THEN

ELSE -- if not inserting, then we are updating

END IF;
end;
/
Re: Urgent - forgotten - Trigger code for audit columns [message #52782 is a reply to message #52780] Tue, 13 August 2002 05:34 Go to previous message
Mahesh Rajendran
Messages: 10707
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
CREATE OR REPLACE TRIGGER IC_BIUR
BEFORE INSERT OR UPDATE OF LAST_NAME, FIRST_NAME
ON INSURANCE_CONTACTS
FOR EACH ROW
begin
/* for inserting only new values
if inserting then
 insert into audit_table values (:new.col1,...:new.coln);
end if;
/* for delete, only Old values */
if deleting then
 insert into audit_table values
 (:old.col1,..,:old.coln);
end if;
/* for update both old and new values */
if updating then
 insert into audit_table values
  (:old.col1,:new.col1.......);
end if;
end;

if update

   

Previous Topic: permission when accessing object in PL/SQL granted by role
Next Topic: Trigger code for audit columns
Goto Forum:
  


Current Time: Sat Apr 20 00:57:35 CDT 2024