trigger that is called AFTER an update FOR EACH ROW affected. This trigger is called on an update to a phone book database. When the trigger is called it adds an entry into a separate table named phone_book_audit. Also take note of triggers being able to take advantage of schema objects like sequences, in this example audit_id_sequence.nexVal is used to generate unique primary keys in the phone_book_audit table. CREATE OR REPLACE TRIGGER phone_book_audit AFTER UPDATE ON phone_book FOR EACH ROW BEGIN INSERT INTO phone_book_audit (audit_id,audit_change, audit_l_name, audit_f_name, audit_old_phone_number, audit_new_phone_number, audit_date) VALUES (audit_id_sequence.nextVal,'Update', :OLD.last_name, :OLD.first_name, :OLD.phone_number, :NEW.phone_number, SYSDATE); END; Now calling an UPDATE on the phone_book table for people with the last name 'Jones'. UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones'; Notice that the phone_number_audit table is now populated with two entries. This is due to the database having two entries with the last name of 'Jones'. Since the update modified two separate row values, the created trigger was called twice; once after each modification. After - statement-level trigger An Oracle syntax statement trigger that is called after an UPDATE to the phone_book table. When the trigger gets called it makes an insert into phone_book_edit_history table CREATE OR REPLACE TRIGGER phone_book_history AFTER UPDATE ON phone_book BEGIN INSERT INTO phone_book_edit_history (audit_history_id, username, modification, edit_date) VALUES (audit_history_id_sequence.nextVal, USER,'Update', SYSDATE); END; Now doing exactly the same update as the above example, however this time with a statement level trigger. UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones'; The result shows that the trigger was only called once, even though the update did change two rows. Before each - row-level trigger This example demonstrates a BEFORE EACH ROW trigger that modifies the INSERT using a WHEN conditional. If the last name is larger than 10 letters, using the SUBSTR function we change the last_name column value to an abbreviation. CREATE OR REPLACE TRIGGER phone_book_insert BEFORE INSERT ON phone_book FOR EACH ROW WHEN (LENGTH(new.last_name) > 10) BEGIN :new.last_name := SUBSTR(:new.last_name,0,1); END; Now performing an INSERT of someone with a large name. INSERT INTO phone_book VALUES (6, 'VeryVeryLongLastName', 'Erin', 'Minneapolis', 'MN', '989 University Drive', '123-222-4456', 55408, TO_DATE('11/21/1991', 'MM/DD/YYYY')); The trigger worked as per the result above, modifying the value of the INSERT before it was executed. Before - statement-level trigger Using a BEFORE statement trigger is particularly useful when enforcing database restrictions. This example demonstrate how to enforce a restriction upon someone