|
|
Oracle triggers are very powerful. Oracle supports BEFORE, AFTER, and INSTEAD OF triggers.
AFTER triggers execute after the intended SQL command executes; they fire only if the SQL command doesn't break any constraints existing on that data table. BEFORE and INSTEAD OF triggers fire before the command executes (so before any constraints are checked), allowing you to perform changes on the actions originally intended by the user.
With Oracle you can have statement-level triggers or row-level triggers. With statement-level triggers, a SQL command that affects multiple rows will execute the trigger only once (just like with SQL Server). In Oracle, statement-level triggers are most often used to enforce various security measures on the actions that can be performed on the data, rather than actually controlling and modifying the data that gets changed, inserted, or deleted.
Row-level triggers are invoked for each row that's affected by the SQL command. If a single query updated 100 rows, the row-level trigger is called 100 times for each inserted row. Inside a row-level trigger, you can refer to the old and new values of the row for which the trigger was invoked using special accessors. These are called by default :OLD and :NEW, but the CREATE TRIGGER syntax permits changing these default names.
You can access the row being inserted using :NEW and the row being deleted with :OLD. An UPDATE trigger populates both :NEW and :OLD.
| Tip |
After writing a trigger you may be told that it wasn't correctly compiled. If this is the case, you can use the SHOW ERRORS command to see a detailed list with the errors. |
Oracle also supports database-level triggers and schema-level triggers, which are useful for automating database maintenance and audition actions or for providing additional database security monitoring. Schema-level triggers can watch for CREATE TABLE, ALTER TABLE, and DROP TABLE events and react to them. You can set database-level triggers to fire on database events such as errors, logons, log offs, shutdown operations, and startup operations.
You start by creating the FriendAudit table that will hold the audit information:
CREATE TABLE FriendAudit (FriendAuditID INT PRIMARY KEY NOT NULL, Operation VARCHAR(10), RecordedOn DATE DEFAULT SysDate, OldName VARCHAR(50), NewName VARCHAR(50), OldPhone VARCHAR(15), NewPhone VARCHAR(15));
In this code, we supplied the SysDate function as the default value for the RecordedOn field (this has a similar result to using CURRENT_DATE, which you saw in the previous chapter), so whenever you add new values to FriendAudit without specifying a value for RecordedOn, the current date and time will be automatically supplied.
Let's also create a sequence named FriendAuditIDSeq, which you'll use to generate unique ID values for FriendAudit:
CREATE SEQUENCE FriendAuditIDSeq;
So, let's start by creating a simple trigger that logs INSERT operations that happen to Friend.
Create the trigger using the following CREATE TRIGGER command:
CREATE TRIGGER FriendAuditTrigger AFTER INSERT ON Friend FOR EACH ROW BEGIN INSERT INTO FriendAudit (FriendAuditID, Operation, NewName, NewPhone) VALUES (FriendAuditIDSeq.NEXTVAL, 'Insert ', :NEW.Name, :NEW.PhoneNo); END; /
Remember that you can use SHOW ERRORS to see what happened if anything went wrong.
Let's test the trigger now. Add three records to Friend using the following queries:
INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');
Now you can query FriendAudit and see that your trigger worked as expected. You created FriendAuditTrigger as an AFTER INSERT, row-level trigger:
CREATE TRIGGER FriendAuditTrigger AFTER INSERT ON Friend FOR EACH ROW
Because it's an AFTER trigger, it's invoked only after all other constraints have been checked and the new row has been introduced. This is what you need because in this case you want to log only the operations that have succeeded— you're not interested in recording failed attempts.
If you wanted to also record failed attempts, you could have used a BEFORE trigger or both BEFORE and AFTER triggers—remember that you're allowed to place multiple triggers on a single table.
The body of the trigger gets data from :NEW and saves it into FriendAudit:
BEGIN INSERT INTO FriendAudit (FriendAuditID, Operation, NewName, NewPhone) VALUES (FriendAuditIDSeq.NEXTVAL, 'Insert ', :NEW.Name, :NEW.PhoneNo); END;
Note that you use the FriendAuditIDSeq sequence to generate a value for the primary key.
Oracle permits adding conditions that control whether the trigger should execute. You do this with the WHEN parameter in CREATE TRIGGER.
With Oracle, instead of CREATE TRIGGER, you use CREATE OR REPLACE TRIGGER. With this command, if the trigger already exists, it's simply replaced by the new definition, without requiring you to drop the trigger first. If the trigger already exists, CREATE OR REPLACE TRIGGER has the same effect as SQL Server's ALTER TRIGGER.
The following trigger only logs INSERT operations when the name to be inserted is Peter:
CREATE OR REPLACE TRIGGER FriendAuditTrigger AFTER INSERT ON Friend FOR EACH ROW WHEN (NEW.Name='Peter') BEGIN INSERT INTO FriendAudit (FriendAuditID, Operation, NewName, NewPhone) VALUES (FriendAuditIDSeq.NEXTVAL, 'Insert ', :NEW.Name, :NEW.PhoneNo); END; /
In order to test the trigger, let's first clear the Friend and FriendAudit tables:
DELETE FROM Friend; DELETE FROM FriendAudit;
Now execute the INSERT statements again:
INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');
You'll see that only the last insert was logged into FriendAudit. This happens because you added the WHEN parameter when creating the trigger:
WHEN (NEW.Name='Peter')
This makes the trigger execute only when the Name column of the inserted row has the value of 'Peter'. Note that here you don't place : before NEW.
Okay, you've learned how to log INSERTS. Let's now complicate the trigger to log all operations: inserts, updates, and deletes. Let's first write the new trigger, and then we'll comment upon it:
CREATE OR REPLACE TRIGGER FriendAuditTrigger
AFTER INSERT OR DELETE OR UPDATE ON Friend
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO FriendAudit (FriendAuditID, Operation, NewName, NewPhone)
VALUES (FriendAuditIDSeq.NEXTVAL, 'Insert ', :NEW.Name, :NEW.PhoneNo);
ELSIF DELETING THEN
INSERT INTO FriendAudit (FriendAuditID, Operation, OldName, OldPhone)
VALUES (FriendAuditIDSeq.NEXTVAL, 'Delete ', :OLD.Name, :OLD.PhoneNo);
ELSIF UPDATING THEN
INSERT INTO FriendAudit (FriendAuditID, Operation,
OldName, OldPhone, NewName, NewPhone)
VALUES (FriendAuditIDSeq.NEXTVAL, 'Update ',
:OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo);
END IF;
END;
/
Let's test the new trigger. First, clear the existing the tables, in this order:
DELETE FROM Friend; DELETE FROM FriendAudit;
Second, execute the following operations:
INSERT INTO Friend(Name, PhoneNo) VALUES('Jerry', '001-Jerry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Harrison', '999-Harry');
INSERT INTO Friend(Name, PhoneNo) VALUES('Peter', '223223223');
UPDATE Friend SET PhoneNo = 'Unknown';
DELETE FROM Friend;
Now if you read the FriendAudit table, you'll see there are three INSERT operations, three UPDATE operations, and finally three DELETE operations logged.
To implement the new functionality, you first instructed the trigger to fire after any INSERT, DELETE, or UPDATE actions:
CREATE OR REPLACE TRIGGER FriendAuditTrigger AFTER INSERT OR DELETE OR UPDATE ON Friend FOR EACH ROW
Inside the trigger you can test the kind of SQL command that caused the trigger to fire using IF conditional statements. If the trigger is fired because of an INSERT operation, you execute the SQL you already know from the previous exercises:
IF INSERTING THEN INSERT INTO FriendAudit (FriendAuditID, Operation, NewName, NewPhone) VALUES (FriendAuditIDSeq.NEXTVAL, 'Insert ', NEW.Name, :NEW.PhoneNo);
The trigger has similar behavior for rows that are being deleted or updated. Note that in case of updated rows, you get information from both :OLD and :NEW, and you store the old and new values into the FriendAudit table.
As you can see, when inserting new records into FriendAudit, you always specify a value for FriendAuditID using the FriendAuditIDSeq sequence.
Using a sequence and a trigger, you can simulate in Oracle the autonumbering functionality that's available with SQL Server, DB2, MySQL, and Access.
For the purpose of this example, let's assume that you don't want to manually supply a new value for FriendAuditID when inserting a new row into FriendAudit— you want the database do this for you.
The following code shows how to create the FriendAuditIDSeq sequence and how to use it in a trigger named FriendAuditIDAutonumberTrigger. This is a BEFORE INSERT trigger; it specifies a value for the FriendAuditID before the actual INSERT happens:
CREATE SEQUENCE FriendAuditIDSeq; CREATE OR REPLACE TRIGGER FriendAuditIDAutonumberTrigger BEFORE INSERT ON FriendAudit FOR EACH ROW BEGIN SELECT FriendAuditIDSeq.NEXTVAL INTO :NEW.FriendAuditID FROM DUAL; END; /
So, this time you have a BEFORE INSERT trigger. This is a must because you need to supply a value for the ID column before this is added to the database.
The trigger's body sets the FriendAuditID column to the next value of the sequence; it's exactly what you were doing manually in the past, except this time it's all handled by the trigger:
SELECT FriendAuditIDSeq.NEXTVAL INTO :NEW.FriendAuditID FROM DUAL;
Note this trigger supplies a new value, without checking if you supplied our own; in other words, if you specify your own ID values, they'll be ignored (or better said, overwritten).
Once the trigger is in place, the technique of finding out the last number generated is still the same: FriendAuditIDSeq.CurrVal returns the current value of the sequence. The following simple query types the current value of the sequence:
SELECT FriendAuditIDSeq.CurrVal FROM Dual;
After having the autonumbering functionality in place, you don't need to manually specify values when inserting new ID values to FriendAudit. If you do specify any values, they're overridden by FriendAuditIDAutonumberTrigger anyway. In this case, FriendAuditTrigger should look like this:
CREATE OR REPLACE TRIGGER FriendAuditTrigger
AFTER INSERT OR DELETE OR UPDATE ON Friend
FOR EACH ROW
BEGIN
IF INSERTING THEN
INSERT INTO FriendAudit (Operation, NewName, NewPhone)
VALUES ('Insert ', :NEW.Name, :NEW.PhoneNo);
ELSIF DELETING THEN
INSERT INTO FriendAudit (Operation, OldName, OldPhone)
VALUES ('Delete ', :OLD.Name, :OLD.PhoneNo);
ELSIF UPDATING THEN
INSERT INTO FriendAudit (Operation, OldName, OldPhone, NewName, NewPhone)
VALUES ('Update ', :OLD.Name, :OLD.PhoneNo, :NEW.Name, :NEW.PhoneNo);
END IF;
END;
/
In this newer version of FriendAuditTrigger you don't specify your own values for the FriendAuditID column anymore. Repeat the tests from the previous test, and you'll see that everything works as expected.
There are times when you want to temporarily disable existing triggers. A common scenario for this is during large data loads into the database, when disabling the triggers can considerably improve the performance.
| Tip |
When created, triggers are enabled by default. |
To manually enable and disable triggers, you use the ALTER TRIGGER command. Here are the commands that enable and disable FriendAuditTrigger:
ALTER TRIGGER FriendAuditTrigger ENABLE; ALTER TRIGGER FriendAuditTrigger DISABLE;
You can also enable and disable triggers on a per-table basis. The following two statements disable, and respectively enable, all the triggers associated with the Friend table. Remember that a table can have more triggers associated with it:
ALTER TABLE Friend DISABLE ALL TRIGGERS; ALTER TABLE Friend ENABLE ALL TRIGGERS;
|
|