Start Trigger updating same table

Trigger updating same table

We could design two separate tables: a user logon table and a user logoff table. In order to get around this table issue, we can create a database design whereby a single table is used to record both logon and logoff events (Listing A).

This can be a tricky process that presents a lot of opportunity for error. In addition, we'll add a field to compute the elapsed minutes for each user's specific session.

This pre-computation done by the trigger saves time and makes for a much more informative report, as we'll see later.

However, these data types are included for backward compatibility purposes only.

The preferred storage for large data is to use the varchar(max), nvarchar(max), and varbinary(max) data types.

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

The first step is to create an Oracle table that can store the information gathered by the end-user logon/logoff triggers.

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.

In order to properly design these triggers, let's begin by looking at the information that's available inside the system-level triggers.

First, we'll gather the information provided at login: Since the user logon/logoff triggers are separate entities, we have several choices in the design of a table to support this information.

Both the event and the resultant action typically happen within the database. Updating the table The operation on which trigger gets executed and the work performed at that time depends on the statements written in the trigger.