Home » RDBMS Server » Security » Trigger code for audit columns
Trigger code for audit columns [message #39805] Tue, 13 August 2002 05:03 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: Trigger code for audit columns [message #39808 is a reply to message #39805] Tue, 13 August 2002 08:18 Go to previous messageGo to next message
Keith
Messages: 88
Registered: March 2000
Member
For example:

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

SELECT user, sysdate INTO :new.inserted_by, :new.insert_date FROM DUAL;

ELSE -- if not inserting, then we are updating

SELECT user, sysdate INTO :new.updated_by, :new.update_date FROM DUAL;

END IF;
end;
/
Re: Trigger code for audit columns [message #39813 is a reply to message #39805] Tue, 13 August 2002 09:44 Go to previous message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Just a note that you don't need to select from DUAL here:

:new.inserted_by := user;
:new.insert_date := sysdate;
Previous Topic: Urgent - forgotten - Trigger code for audit columns
Next Topic: How to grant execute privileges for some procedures and not to grant to some other procedures of the
Goto Forum:
  


Current Time: Mon Oct 18 06:08:07 CDT 2021