Triggers & Transactions
The SQL Developer interface shows the possibilities:
· before or after insert, delete, update
· statement level: once for each triggering statement
· row level: once for each modified tuple
----- Cascading Trigger -----
Suppose we wish to cascade an update of RoomNr in Room to RoomNr in Reservation. (If, by chance, we decide to change the room number of a room, the reservations for the room number will also automatically change.)
We specify the triggering action using the template:
This generates SQL DDL, which we can see by clicking on the DDL tab.
Now we must write code to specify what will happen when the trigger fires. To edit the code, we right click on the trigger we’ve just created and the click on edit. We then type in the statement(s) we want. (Note that the database variables must have a preceding colon.)
Now when we update a room number in Room, the update will automatically propagate to the room number in Reservation.
RoomNr in Room for Room 1 is now 10 – AND all reservations for Room 1 have been changed to Room 10.
----- Potentially Circular Triggers -----
Example: Suppose we don’t want the cost of the rooms to drop.
But this fails (even fails to compile) – any idea why? (Oracle won’t let a trigger modify the table from which it is triggered. In general this may lead to an infinite loop.)
So how do we solve this problem? (We can raise an error and print an error message. The system automatically does a rollback so that the modification will not take place.)
Now if we try to decrease the Cost for Room 1 from 90 to 70, the trigger will raise an error.
Executing the same query, only on Room 5 is OK, since Room 5’s Cost is 50, and 70 is up.
We can drop a trigger either by right clicking on the trigger and then clicking on Drop Trigger. Or we can execute an SQL Drop Trigger statement, e.g. Drop Trigger COSTONLYUP. We must drop the trigger in order to restore the Green room to have a Cost of 50 (because, with the trigger, we can only update the table by raising rates).
To return to our initial state, assume we have restored the Cost of the Green room to be 50: update Room set Cost = 50 where RoomNr = 5.
----- InsteadOf Triggers -----
We can also use triggers to do view updates. Recall that to do a view update, we need to update the base tables.
To create a view in Oracle SQL Developer, we right click on Views and then click on New View. We then edit the select statement. Once a view exists, we can right click on it and do the editing in an Edit View Window.
We can then execute queries on the view.
Now, suppose we learn that Smith 101’s name is really Smith-Jones. If we try to update the view with update Res_10May set Name = 'Smith-Jones' where GuestNr = 101, we get an error.
Recall, when updating a view, we must actually update the base relations, not the view. Sometimes the database knows (or thinks it knows) how to do this and will do it for us. When it doesn’t know, or when it guesses wrong, we can use an “instead of” trigger to do the update we want.
To declare an instead of trigger using SQL developer, open a new trigger and click on the pull-down menu for Trigger Type and select VIEW.
Now, when we execute update Res_10May set Name = 'Smith-Jones' where GuestNr = 101, we update the Name of 101 Smith in the Guest table and thus in the view. (Note: I’ve hard-coded 101 into the query for the example; to make this reasonable, we would want to embed this update trigger in code that would let us choose the GuestNr as a variable.)
Suppose we wish to remove Smith 101 altogether. (We first rollback – click on the red reverse arrow – which reverts the database to the state before we updated Smith.) If we execute delete from Res_10May where GuestNr = 101, surprisingly Oracle does not complain.
Here’s the view after the delete, which looks correct.
Here’s the Reservation table after the delete. (Note that 101’s 10 May reservation is gone, which is also what we want.)
But here’s the Guest table after the delete. (Note that Guest 101 is still there.) It appears that Oracle must, as a default, choose to handle a delete in a view created by a join by deleting (only) from the table that has a foreign key reference to the other table. Often this is what we want. If we had wanted to only delete Smith 101’s 10 May reservation, this would have been correct.
To make the view update do what we want, we rollback and define an instead of trigger.
Now when we execute the trigger, we get the following.
Here’s the view. (101 is gone.)
Here’s the Guest table. (101 is gone.)
And here’s the reservation table. (All of 101’s reservations are gone.)
We can restore the tables with rollback (several, since we caused multiple updates).
Constraint checking that requires more than one statement (leading to transactions)
Here ‘s the so-called Chicken-and-Egg problem. Suppose we wish to declare two tables that mutually depend on each other – insertion/deletion/update of one requires insertion/deletion/update of the other. We wish to declare mutual foreign-key constraints as follows.
But Oracle won’t let us because we cannot reference a table that has not yet been created. Either order here fails and gives an error when we try to execute this sequence of two statements.
To resolve this problem, we initially create the tables without the foreign-key references.
(For some reason, Oracle made me create these one at a time – the following should work, but didn’t. ??)
We then alter the tables, adding the foreign-key references. (Strange – but when I executed the following, only the second foreign key – the one for EGG was added. I edited CHICKEN and added the other foreign key by hand.)
Next we try to insert a tuple into either Chicken or Egg. It won’t work, and shouldn’t because of the referential integrity constraint. Indeed, we can never insert tuples into either Chicken or Egg because neither insert can be executed first.
Executing raises the following error.
To make this work we need to defer the checking of the constraints. To do so, we alter the constraints, declaring them to be deferrable.
Now when we try to insert (1, 2) into Chicken, it works – no error.
We continue and insert (2, 1) into Egg, which also works.
Now, Chicken’s eID value is 2, which is Egg’s primary-key value, and Egg’s eID value is 1, which is Chicken’s primary-key value, so the referential integrity constraints hold. We now click on commit (the green check mark icon). There’s no error – the check has been deferred to the commit. Further when we display Chicken and Egg, the tuples are present.
The constraints do work, however. Suppose we try to insert (3, 3) into Chicken.
We execute and everything’s OK (the system is waiting for a commit before checking). When we now click on commit, we get an error.
The system considers the actions to be a transaction (see error message above). Transactions are several statements that execute together. Either all statements execute or none execute. A transaction is a sequence of steps, executed as a unit that takes the database from one valid state to another.
----- Transactions -----
1. We make changes in the database with the statement COMMIT.
2. All changes since the previous COMMIT are committed.
3. If we do not want the changes committed, we execute the statement ROLLBACK.
4. Statements between COMMITs or between COMMIT/ROLLBACK constitute transactions.
5. It’s typical to group statements into a block or procedure to do transactions. Such a group typically ends with COMMIT or ROLLBACK. (ROLLBACKs are typically guarded by IF statements – only done under certain conditions.)
Let’s create a procedure to make a reservation – insert a tuple in Guest and Reservation.
Right click on Procedures and select New Procedure. Then fill in the Create Procedure dialog box, which lets you name procedures and give their parameters.
Clicking on OK yields editable code. We fill in the BEGIN-END part with the statements we wish to execute.
We can now compile and execute. The usual way to execute is to call the procedure while executing other code, such as a trigger.
CALL Make_Reservation(107, ‘Adams’, ’15 Elm’, ‘Boston’, 5, ’10 May’, 2)
If we wish to execute this as a stand-alone procedure (which we do for our example here), we right click on the procedure and select Run. We then have to edit the code to fill in the parameters, which appear as assignment statements.
We then click OK and the procedure executes.
When the procedure has executed, we can see the results by displaying the tables that have been updated.
Suppose we try to execute the same procedure a second time. (What will happen?)
Now we can make much fancier transactions/procedures. Check SQL Developer help for examples. Here’s one from one of the help pages.
Observe that the language resolves the impedance mismatch using embedded SQL. The language is Oracle’s, as used in SQL Developer. Note that the colon precedes ordinary programming-language variables when the variable is used in an SQL statement (e.g. :in_rating). Note that Oracle does not require the programmer to mark SQL statements for the preprocessor. Apparently, they have gone to the work of fully integrating them into the language.