Trigger code for audit columns [message #39805] |
Tue, 13 August 2002 05:03 |
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 |
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;
/
|
|
|
|