everythingOracle.bizhat.com

 

'Everything you wanted
to know about Oracle'

Training References Syntax Tell A Friend Contact Us

 

 

PL/SQL

01 02 03 04 05 06 07 08 09 10 11 12 13 14 15 16 17 18 19 20 21 22
<< Previous

Chapter # 16

Next >>


 

Developing and using Database Triggers

 

Hands-On introduction

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 will create a trigger to audit department table (dept) to keep track of all the insert, update, and delete transactions.

For example: If oracle inserted a department record for the department number 50, then the message should say �oracle inserted deptno: 50�

 

Introduction:

A database trigger is a set of PL/SQL statements that execute each time an event such as an update, insert, or delete statement occurs on the database. They are similar to stored PL/SQL statements. They are stored in the database and attached to a table.

There are two types of database triggers: statement triggers and row triggers. A statement trigger will fire only once for a triggering statement. A row trigger fires once for every row affected by a trigger statement. Triggers can be set to fire either before or after Oracle processes the triggering insert, update, or delete statement.

The keywords updating, deleting, or inserting can be used when multiple triggering events are defined. Once you create the trigger. It is enabled and ready to execute. You can enable or disable the trigger. Remember that No special privileges other than permission to access to the table is needed to run the trigger.

Display Database Objects

Click on the �+� sign to expand the item. Expand "Database Objects.� Expand the oracle schema. Expand �tables� Expand the �Dept� table. An empty box means the �dept� table has no triggers and the �+� sign means there at least one object in the selected item.

 

Go to MS-DOS.

Login to �sqlplus� as oracle password learning

 

Create audit table

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

SQL> CREATE TABLE audit_dept

(audit_line VARCHAR2(80);

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."

PL/SQL> SELECT * FROM audit_dept;

Create a trigger

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.

(Procedure Builder-Creating New Trigger)

BEGIN

-- audit if the user inserted a record�

IF INSERTING THEN

INSERT INTO audit_dept

VALUES (user || � inserted deptno: � || :new.deptno);

-- audit if the user updated a record�

ELSIF UPDATING THEN

INSERT INTO audit_dept

VALUES (user || � updated deptno: � || :old.deptno);

-- audit if the user deleted a record�

ELSIF DELETING THEN

INSERT INTO audit_dept

VALUES (user || � deleted deptno: � || :old.deptno);

-- end if

END ID;

END;

Compile a trigger

Click save to compile. Then close the window.

Display a table�s trigger

Expand the triggers. You will see the trigger.

DISABLE or ENABLE a trigger

You can disable or enable the trigger by clicking on left mouse while trigger is highlighted.

Or

PL/SQL> ALTER TRIGGER oracle.audit_dept_table DISABLE:

PL/SQL> ALTER TRIGGER oracle.audit_dept_table ENABLE:

Query the dept and audit_dept tables.

PL/SQL> SELECT * FROM dept;

PL/SQL> SELECT * FROM audit_dept;

Notice there are no records in the audit_dept table.

Test a trigger

Insert a record into the dept table. Save the inserted transaction.

PL/SQL> INSERT INTO dept

VALUES (40,�Finance�,�Ohio�);

PL/SQL> COMMIT;

 

Query the audit dept table.

PL/SQL> SELECT * FROM audit_dept;

It shows that the oracle user inserted a department record.

 

Update a record from the dept table. Save the updated transaction.

PL/SQL> UPDATE dept

SET loc = �Washington, DC�

WHERE deptno = 40;

PL/SQL> COMMIT;

 

Then query the audit dept table.

PL/SQL> SELECT * FROM audit_dept;

It shows that the oracle user updated a department record.

 

Delete a record from the dept table. Save the deleted transaction.

PL/SQL> DELETE FROM dept

WHERE deptno = 40;

PL/SQL> COMMIT;

 

Then query the audit dept table.

PL/SQL> SELECT * FROM audit_dept;

It shows that the oracle user deleted a department record.

Open a trigger

Double click on the trigger icon to open the trigger. You can change the trigger.

Drop a trigger

In the trigger window, click on the �DROP� button to drop the trigger. Then confirm the deletion.

Trigger was deleted.

- OR -

PL/SQL> DROP TRIGGER �audit_dept_table�;

 

Questions:

Q: What is a database trigger?

Q: How do you create a trigger?

Q: If you drop a table that contains a trigger, does its trigger drop?

Q: Create a trigger to audit department table (dept) to keep track of all the insert, update, and delete transactions and insert the audited transaction to a table.

Q: How do you compile a trigger?

Q: How do you disable or enable a trigger?

Q: How do you test your created trigger?

Q: How do you modify a trigger?

Q: How do you drop a trigger?

Q: When you drop a trigger, does its table drop?

     Reviews and Templates for FrontPage
     

Copyright � everythingOracle.bizhat.com 2006 All Rights Reserved.