A NOTE REGARDING STORED PROCEDURES AND TRIGGERS:
By enforcing custom data integrity (Business Rules) using Stored Procedures and Triggers, enforcement can be centralized at the Server. This will keep network traffic to a minimum.
WHAT IS A TRIGGER?
A Trigger is a procedure you associate with a table. When you issue a SQL Statement for a table which meets a Trigger Condition, Oracle automatically fires the Trigger's Body. You can use Triggers to program the Oracle Server to react to specific situations such as enforcing data integrity rules or deriving specific values. A Trigger Condition has a Triggering Statement and an optional Trigger Restriction. A Triggering Statement is an Insert, Update or Delete Statement which references a specific table. A Trigger Restriction is a simple Boolean condition you specify in an optional When clause of the Create Trigger Command.
This Trigger was created using SQL Plus to Oracle. The Trigger will store the Customer Last Name, First Name and Customer ID in a Customer History Table when a Delete Customer is Performed on the Customer Table. Notice that the fields are separated by commas, and that the entire statement is ended with a semi-colon which is referred to in Oracle as a Statement Terminator. Also, the :Old. which preceeds the Customer History Table's Field Names is an Oracle Assignment Binding Operator
FYI: If a user deletes multiple rows with one SQL Statement, the DeleteCustomer Trigger fires once before the deletion of Each customer record.
NOTE: For purposes of clarity, I have listed the items on individual lines. However, it is not necessary to use this convention in SQL Plus/Oracle. You may have one long statement; however, this format is much more readable.
CREATE OR REPLACE TRIGGER DeleteCustomerBEFORE DELETE ON Customer
FOR EACH ROW
BEGIN
INSERT INTO CustomerHistory
VALUES(:Old.CUSTOMERID, :Old.LastName, :Old.FirstName);
END DeleteCustomer;