探究Oracle中不同触发器类型的特点（oracle触发器类型）
The Oracle Database provides support for four types of triggers: Before, Row, Instead Of and After. Each type of trigger has its own characteristics and may be used differently. Understanding the differences and functions of each type of trigger is a vital step to creating and managing powerful database applications.
Before Triggers
Before triggers are fired before the database makes any changes to the data in the database table. A Before trigger fires just before the triggering statement is executed. This type of trigger is useful when you want to validate data before an operation occurs and prevent invalid transactions from occurring.
For example, a Before trigger could be used to check the values in a master table and ensure that information entered into a related detail table is valid and consistent. The following example shows the syntax used to create a Before trigger in the Oracle database:
CREATE OR REPLACE TRIGGER trg_validation
BEFORE INSERT OR UPDATE ON table1
FOR EACH ROW
BEGIN
IF :old.value1 = :new.value2
THEN
RAISE_APPLICATION_ERROR(-20001,’Invalid Table1 Data’);
END IF;
END;
Row Triggers
Row triggers fire once for each row processed by the triggering statement. This type of trigger is useful for performing operations on the data in the row itself or making calculations based on the values in the row. This type of trigger is often used in data transformation tasks to perform operations on each row before inserting that row into a different table or to assign values to the other rows.
The following example shows a Row trigger that updates the value of one column based on the value of another:
CREATE OR REPLACE TRIGGER trg_calc_col
BEFORE INSERT OR UPDATE
ON table1
FOR EACH ROW
BEGIN
:new.col2 := :new.col1 * 0.5;
END;
Instead Of Triggers
Instead Of triggers are used when you need to make changes to the data before it is actually stored in the database table. For example, if you have an application where you want to change the data as it is saved but not actually store the changes in the database, an Instead Of trigger can be used. This type of trigger fires before the operation is actually performed, then once the operation has been performed the trigger can undo the changes.
The following example shows an Instead Of trigger that will update a table even though the underlying table has not yet been modified:
CREATE OR REPLACE TRIGGER trg_update
INSTEAD OF INSERT OR UPDATE
ON table1
FOR EACH ROW
BEGIN
UPDATE table1
SET col2 := :new.col2
WHERE col1 = :old.col1;
END;
After Triggers
After triggers are fired after an operation has been completed. These triggers are used to perform any post-operation processing such as updating summary tables or sending out notifications when a certain operation has occurred. The following example shows the syntax used to create an After trigger:
CREATE OR REPLACE TRIGGER trg_notify
AFTER INSERT OR UPDATE
ON table1
FOR EACH ROW
BEGIN
SEND_EMAIL_NOTIFICATION(:old.col1);
END;
Conclusion
Each type of Oracle trigger has its own set of benefits and uses. Understanding the characteristics of each type is essential to creating powerful applications that can be maintained easily. Knowing when and how to use each type of trigger can greatly enhance the performance and reliability of an application.
