Editing Database trigger (section)

== Row and statement level triggers ==
To understand how trigger behavior works, you need to be aware of the two main types of triggers; these are Row and Statement level triggers. The distinction between the two is how many times the code within the trigger is executed, and at what time.

Suppose you have a trigger that is made to be called on an UPDATE to a certain table. Row level triggers would execute once for each row that is affected by the UPDATE. It is important to keep in mind if no rows are affected by the UPDATE command, the trigger '''will not''' execute any code within the trigger. Statement level triggers will be called once '''regardless''' of how many rows are affected by the UPDATE. Here it is important to note that even if the  UPDATE command didn't affect any rows, the code within the trigger will still be executed once.

Using the BEFORE and AFTER options<ref>{{Cite web|url=https://docs.oracle.com/cd/B25329_01/doc/appdev.102/b25108/xedev_triggers.htm|title=6 Using Triggers|website=docs.oracle.com}}</ref>  determine when the trigger is called. Suppose you have a trigger that is called on an INSERT to a certain table. If your trigger is using the BEFORE option, the code within the trigger will be executed before the INSERT into the table occurs. A common use of the BEFORE trigger is to verify the input values of the INSERT, or modify the values accordingly. Now let's say we have a trigger that uses AFTER instead. The code within the trigger is executed after the INSERT happens to the table. An example use of this trigger is creating an audit history of who has made inserts into the database, keeping track of the changes made. When using these options you need to keep a few things in mind. The BEFORE option does '''not allow''' you to modify tables, that is why input validation is a practical use. Using AFTER triggers allows you to modify tables such as inserting into an audit history table.

When creating a trigger to determine if it is statement or row level simply include the FOR EACH ROW clause for a row level, or omit the clause for a statement level. Be cautious of using additional [[Insert (SQL)|INSERT]]/[[Update (SQL)|UPDATE]]/[[Delete (SQL)|DELETE]] commands within your trigger, because trigger [[Recursion (computer science)|recursion]] is possible, causing unwanted behavior. In the examples below each trigger is modifying a different table, by looking at what is being modified you can see some common applications of when different trigger types are used.

The following is an Oracle syntax example of a row level 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,<ref>{{Cite web|url=http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm|archive-url=https://web.archive.org/web/20111201225844/http://docs.oracle.com/cd/B12037_01/server.101/b10759/statements_6014.htm|url-status=live|archive-date=2011-12-01|title=Oracle's Documentation on Sequences}}</ref> in this example audit_id_sequence.nexVal is used to generate unique [[primary key]]s in the phone_book_audit table.

<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

Now calling an [[Update (SQL)|UPDATE]] on the phone_book table for people with the last name 'Jones'.
<syntaxhighlight lang="sql">
UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';
</syntaxhighlight>

{| class="wikitable"
|-
! Audit_ID !! Audit_Change !! F_Name !! L_Name !! New_Phone_Number !! Old_Phone_Number !! Audit_Date
|-
| 1 || Update || Jordan || Jones || 111-111-1111 || 098-765-4321 || 02-MAY-14
|-
| 2 || Update || Megan || Jones || 111-111-1111 || 111-222-3456 || 02-MAY-14
|}


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
<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

Now doing exactly the same update as the above example, however this time with a statement level trigger. <syntaxhighlight lang="sql">
UPDATE phone_book SET phone_number = '111-111-1111' WHERE last_name = 'Jones';
</syntaxhighlight>

{| class="wikitable"
|-
! Audit_History_ID !! Username !! Modification !! Edit_Date
|-
| 1 || HAUSCHBC || Update || 02-MAY-14
|}

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<ref>{{Cite web|url=https://www.databasestar.com/oracle-sql-functions/|title=Oracle SQL Functions – The Complete List|date=December 26, 2014}}</ref> we change the last_name column value to an abbreviation.

<syntaxhighlight lang="sql">
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;
</syntaxhighlight>

Now performing an INSERT of someone with a large name.
<syntaxhighlight lang="sql">
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'));
</syntaxhighlight>

{| class="wikitable"
|-
! Person_ID !! Last_Name !! First_Name !! City !! State_Abbreviation !! Address !! Phone_Number !! Zip_code !! DOB
|-
| 6 || V || Erin || Minneapolis || MN || 989 University Drive || 123-222-4456 || 55408 || 21-NOV-91
|}

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.<ref>{{Cite web|url=https://docs.oracle.com/cd/B28359_01/appdev.111/b28370/create_trigger.htm|title=Database PL/SQL Language Reference|website=docs.oracle.com}}</ref> This example demonstrate how to enforce a restriction upon someone named "SOMEUSER" on the table phone_book.

<syntaxhighlight lang="sql">
CREATE OR REPLACE TRIGGER hauschbc 
  BEFORE INSERT ON SOMEUSER.phone_book
BEGIN
    RAISE_APPLICATION_ERROR (
         num => -20050,
         msg => 'Error message goes here.');
END;
</syntaxhighlight>

Now, when "SOMEUSER" is logged in after attempting any INSERT this error message will show:

 SQL Error: ORA-20050: Error message goes here.

Custom errors such as this one has a restriction on what the num variable can be defined as. Because of the numerous other pre-defined errors this variable must be in the range of −20000 to −20999.