|
“Life is a foreign
language; all men mispronounce it.” Christopher Morley (1890 -
1957) |
Read
first then play the video:
PLS018(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 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
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?
|