iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - PL/SQL 

 

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

 

Lesson 16

“Life is a foreign language; all men mispronounce it.” Christopher Morley (1890 - 1957)

 

Read first then play the video:

   PLS-VIDEO -Developing and using Database Triggers

  

Section 5: Developing and using Database Triggers

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 iself inserted a department record for the department number 50, then the message should say “isef 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 iself 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 iself password schooling.

 

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 IF;

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 iself.audit_dept_table DISABLE;

PL/SQL> ALTER TRIGGER iself.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 iself 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 iself 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 iself 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”;

 

“Love is not enough. It must be the foundation, the cornerstone - but not the complete structure. It is much too pliable, too yielding.” Bette Davis (1908 - 1989)

 

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?