Oracle-L: RE: What Attributes are available to a Trigger

From: Richard Huntley <rhuntley_at_mindleaders.com>
Date: Tue, 25 Jun 2002 13:30:28 -0800
Message-ID: <F001.00487985.20020625133028@fatcity.com>

You can do something like this:

create or replace trigger LogChanges
before insert or delete or update on table1 for each row
declare
v_changetype char(1);
begin
if inserting then

v_changetype := 'I';
elseif updating then

v_changetype := 'U';
else

v_changetype := 'D';
end if;

insert into my_audit_table
(change_type,changed_by,timestamp,old_val,new_val) values
(v_changetype,USER,SYSDATE,:old.val,:new.val); end;

Then, you'll have all the info you'd need to reconstruct the insert, update or delete statement.

-----Original Message-----
Sent: Tuesday, June 25, 2002 4:29 PM
To: Multiple recipients of list ORACLE-L

Hi,

I know for example, I can access :new values and :old values. I also thought that I could access the TYPE of ddl that caused the trigger to fire and I am wondering if I have access to the sql that caused the trigger to fire.

I am looking in the application development PL/SQL manual but I am not seeing a list anywhere.

Thanks,

Hannah
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author:
INET: johanna.doran_at_sungard.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Richard Huntley
INET: rhuntley_at_mindleaders.com

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists
--------------------------------------------------------------------