skip to content »

Trigger updating another table

trigger updating another table-73

Please be advised that the initial implementation of system-level triggers for end-user tracking is quite new and, as such, is still a bit lacking in robust functionality.

trigger updating another table-30trigger updating another table-35trigger updating another table-74trigger updating another table-25

As you'll note in Listing C, updating the last action is accomplished by using the SYS context function to grab the action column from the v$session table.Starting with Oracle8i, Oracle introduced special triggers that are not associated with specific DML events (e.g., INSERT, UPDATE, and DELETE).These system-level triggers included database startup triggers, DDL triggers, and end-user login/logoff triggers.To make a single table function for both logon and logoff events, it's first necessary to locate the logon row that is associated with the individual user session.As you might imagine, this is tricky because you may have many users who are signed on with identical user names. HIPAA, the Sarbanes-Oxley Act, and the Gramm-Leach-Bliley Act have all produced serious constraints on Oracle professionals who are now required to produce detailed audit information for Oracle system users.

federal laws have mandated increased security for auditing Oracle user activity.

To get around this limitation, I used the Oracle session ID.

As you know, Oracle assigns a unique session ID into the v$session table for each user logged on to Oracle.

orm some validations) whenever the original table (on which the trigger is created) is selected or inserted, updated or deleted.

  A trigger is used to ensure that certain jobs are automatically done when a predefined event occurs.

Now that we understand the basics, let's take a look at how we can design the user audit table to track user activity.