|
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? |
