Re: trigger question
Date: 24 Mar 1995 13:48:42 GMT
Message-ID: <3kuijq$bua_at_alterdial.UU.NET>
lothmar_at_cscoe.ac.com (Mark Loth - DBA) wrote:
>
> Recently a need has arisen within a project I support to monitor updates and inserts
> for certain database tables. I realize this can be done through auditing, but they
> would like to add USER_NAME and SYS_DATE columns to all monitored tables. I have
> been asked to write a trigger which would enter the appropriate user_name and sysdate
> into these columns for the appropriate row whenever an update or insert occurs on
> these tables. I was able to write a trigger for inserts, but I'm having no luck
> for updates.
>
> Can some please help? Any help would be greatly appreciated,
>
Mark,
I'm not sure what problems you have, but the following is one solution. Assume you have a STOCK table that you want to update the username & date of modifications on. Create the following trigger...
CREATE OR REPLACE TRIGGER STOCK_UPDATE
BEFORE UPDATE ON STOCK
FOR EACH ROW
DECLARE
theDate DATE;
theUser VARCHAR2(30);
BEGIN
SELECT SYSDATE, USER
INTO theDate, theUser
FROM DUAL;
:NEW.SYS_DATE := theDate;
:NEW.USER_NAME := theUser;
END;
That should do the trick. The key is the BEFORE UPDATE clause,
which ensures that the modifications are made to the record before it
is stored in the database. I assume that you were using AFTER UPDATE
in which case your modifications would not be saved.
Hope this helps. Received on Fri Mar 24 1995 - 14:48:42 CET