In this Hands-On, you create a table and name it "audit_dept"  (audit department table).  The table contains only one column (audit_line) and it should be big enough to fit 80 characters.

You create a trigger to audit department table (dept) to keep track of all the insert, update, and delete transactions.

For example:  If iself inserted a department record for the department number 50, then the message should say “isef inserted deptno: 50”

You will:

    1- Creating Triggers.

    2- Using Triggers.

    3- Dropping Triggers.

Expand "Database Objects.”

Expand the iself schema.

Expand “tables”

Expand the “Dept” table.

An empty box means the “dept” table has no triggers.

Go to MS-DOS.

Login to “sqlplus” as iself password schooling.

Create a table and name it "audit_dept"  (audit department table).

Then, you will create a trigger to populate the table to keep track of all the insert, update, and delete transactions.

=

Minimize the window.

=

Query the audit department table from the "PL/SQL interpreter."

=

Create a trigger for the department table.

Select “Triggers” and click on the "create" icon.

=

Click "New."

=

On the name box, type the name of the new trigger "audit department table."

=

Checkmark the update, insert, and delete box.

=

Click on the "Row" radio button.

=

In the trigger body, write a PL/SQL block to check if a record was inserted then write the username, type of transaction, and deptno.

=

Remember, on the insert transaction, you should only use the "new" binding variable.  The "old" binding variable does not make sense.

=

Do the same for deleting and updating a record.

On the update or delete transaction, use the "old" binding variable.

=

Click save to compile.

Then close the window.

=

Expand the triggers.

Here is the trigger.

You can disable or enable the trigger.

=

Query the dept table.

Insert a record into the dept table.

Save the inserted transaction.

Query the audit dept table.

It shows that the iself user inserted a department record.

Update a record from the dept table.

Save the updated transaction.

Then query the audit dept table.

It shows that the iself user updated a department record.

Delete a record from the dept table.

Save the deleted transaction.

Then query the audit dept table.

It shows that the iself user deleted a department record.

Double click on the trigger icon to open the trigger.

You can change or drop the trigger.

Click on “DROP,” to drop the trigger and confirm the deletion.

Trigger was deleted.

Now, you should practice this over and over, until you become a master at it.

Good Luck!