Creating an after update trigger for auditing.
Thursday, June 21, 2012
FTER UPDATE Trigger
An AFTER UPDATE Trigger means that Oracle will fire this trigger after the UPDATE operation is executed.
The syntax for an AFTER UPDATE Trigger is:
CREATE or REPLACE TRIGGER trigger_name
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
AFTER UPDATE
ON table_name
[ FOR EACH ROW ]
DECLARE
-- variable declarations
BEGIN
-- trigger code
EXCEPTION
WHEN ...
-- exception handling
END;
trigger_name is the name of the trigger to create.
Restrictions:
- You can not create an AFTER trigger on a view.
- You can not update the :NEW values.
- You can not update the :OLD values.
For Example:
create or replace trigger Enable_tracking
delete or insert or update on schema.table-name
FOR EACH ROW
DECLARE
v_username varchar2(10);
v_exe v$session.program%TYPE; -- EXE (Program used to connect) - See v$session
v_sid v$session.SID%TYPE; -- SID for this session
v_osuser v$session.osuser%TYPE;
v_machine v$session.machine%TYPE;
v_ok VARCHAR (10);
BEGIN
SELECT SID
INTO v_sid
FROM v$mystat
WHERE ROWNUM < 2;
SELECT program
INTO v_exe
FROM v$session
WHERE SID = v_sid;
SELECT osuser
INTO v_osuser
FROM v$session
WHERE SID = v_sid;
SELECT machine
INTO v_machine
FROM v$session
WHERE SID = v_sid;
SELECT u.sid, u.username,s.sql_text,u.SCHEMANAME,u.OSUSERFROM v$sql s, v$session uWHERE s.hash_value = u.sql_hash_value and u.sid=<pass sid here>;
INSERT INTO sys.audit_table (v_sid,v_exe,v_osuser,v_machine);
END;
Labels: Creating an after update trigger for auditing.
0 comments:
Subscribe to:
Post Comments (Atom)
Powered by Blogger.