NOTE: Free essay sample provided on this page should be used for references or sample purposes only. The sample essay is available to anyone, so any direct quoting without mentioning the source will be considered plagiarism by schools, colleges and universities that use plagiarism detection software. To get a completely brand-new, plagiarism-free essay, please use our essay writing service.
One click instant price quote
... e current user. So, the owner must have appropriate access to all objects referenced by the trigger action. Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system (figure 3, page 6). A trigger stored in the database can include SQL and PL/SQL statements to execute as a unit and can invoke stored procedures. Procedures and triggers differ in the way that they are invoked.
A user, application, or trigger specifically executes a procedure. Triggers are basically fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used. A trigger can also call out to a C procedure, which is useful for computationally intensive operations. The body of the trigger is a conventional PL/SQL statement.
In principle, this body may perform any action allowed in a PL/SQL statement, but in practice there are limits, do to Oracle's conservative approach to avoiding a situation where one trigger performs an action that triggers a second trigger, which triggers a third, and so on. Roughly, the body should not modify the relation on which the trigger is placed, or any relation linked to it by a constraint such as a foreign key constraint. The triggers that we have written adds or removes books from the order and updates the inventory of books and music whenever customers place or cancel orders. The triggers only run upon the actions occurring in the order, books ordered, and music ordered tables.
If any activity occurs elsewhere, the triggers will not fire. In the books table, if a deletion occurs, then the trigger runs. In books ordered and music ordered, the triggers only run when an insertion or deletion occurs. We wrote three triggers that keep track of the ordered items and books / music inventory. If a customer decides to cancel an order, then the ORDERUPDATE trigger removes the item (s) from the books ordered and music ordered tables. It does not do anything until the something is deleted from the orders table.
The other two triggers BOOKSINVUPDATE and MUSICINVUPDATE deal with the book and music inventory. They only run if someone is inserting or deleting into the books ordered or music ordered tables. If a book or music item is removed from the order, then the trigger adds the removed item back into the book or music inventory. If an item is added to the order, then the trigger subtracts the item from the book or music inventory. Although triggers are useful for customizing database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which may be difficult to maintain in a large application.
For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers (figure 4, page 7). Therefore we limited the use of triggers in our database and created them so that they are not too complex. Along with our three triggers, we considered writing a trigger for calculating the order total and subtotal, but decided it would be much more convenient to let the customer team calculate this since they need to present this information to the user before inserting it into the database. By providing the three triggers, the logic is significantly simplified for the customer team since they do not have to adjust the inventory recorded in the books and music tables. Accessibility JDBC make database access in Java very easy. JDBC drivers exist for many database managers as well as having a JDBC to ODBC Bridge, which allows access to almost any database using simple SQL statements.
Users can write SQL statements to the database and as long as there exists a JDBC (or ODBC using the JDBC to ODBC bridge) driver for the database, SQL statements will function normally. These drivers exist for nearly every type of database known to man. In order to maintain a certain level of abstraction, we developed a class that connects the user to the database using JDBC. This allows the other teams to instantiate an object of our class to connect to the database and it hides the lower level details from them. Team members can pass SQL statements through the connection classes' execute method and it will return back the result set.
This will allow us the option, if it should come about, to change the database's location and or type without the database users having to make changes to their code. Due to the specific details that are demanded by the search team we didn't develop prepared statements to return items from the database. Instead the search team defined their own set of methods with the help of some sample SQL statements we supplied, this gives them the freedom to change how they search and return data without us having to change our code. They have the ability to control how they search for items if by titles, author, ect. and how they sort those returns as well. If we developed our own methods then it would confine them to using methods that may not allow them the types of returns that they would desire and it would require changes to be made in two places every time a new search needed to be added.
In the future, we would like to develop prepared statements for the customer team's database interactions because their interactions are fairly static and less likely to change over the development cycle, unlike the search team. An in-depth test and collaboration with the customer team would be needed to develop those methods in order to determine the necessary statements. In the future we would like our connection class to be a servlet or separate persistent entity that interacted with both the customers and search teams own Servlets. That way our connection class would become more abstract and would make it so that other teams don't have to import our code into theirs. Another feature in having our code in a separate entity would allow us to setup different accounts to the database to enhance security. This would also allow us to change our code more easily.
Currently, if we needed to change our code, we would have to send our new class file to the other teams and have them.
Free research essays on topics related to: statements, sql, triggers, trigger, database
Research essay sample on Develop Prepared Statements Pl Sql Statement Triggers
