Oracle Database Trigger Defined

A trigger is a stored sub-program that is similar to a procedure and is automatically executed by the database whenever a specified action occurs. Unlike a procedure, you cannot explicitly call a trigger and execute it. A trigger is always executed implicitly by the database system according to the action, timing, and scope specified by the trigger.
The timing of a trigger specifies whether the trigger is executed before or after the action.
The action of a trigger specifies activity in the database, such as a user updating one or more rows in a table.
The scope of a trigger specifies whether the trigger fires once per row or once per transaction.
Triggers are commonly used to:

1. Add a date and time stamp into a column in the row before inserting the row into the table
2. Verify that the current user is allowed to change the row that was modified, and reject the change if the user is not allowed to make the change
3. Write audit trail records to a log file every time a new row is added to a particular table
4. Update a summary row in a different table whenever data changes in the specified table

Most triggers are designed to fire when data is modified, added, or removed from tables.
The next lesson compares trigger attributes to those of procedures.

A database trigger is a stored subprogram associated with a database table, view, or event. The trigger can be called once, when some event occurs, or many times, once for each row affected by an INSERT, UPDATE, or DELETE statement. The trigger can be called after the event, to record it or take some followup action. Or, the trigger can be called before the event to prevent erroneous operations or fix new data so that it conforms to business rules. In Example 1, the table-level trigger fires whenever salaries in the employees table are updated.
For each update, the trigger writes a record to the emp_audit table. Example 1 Creating a Database Trigger

CREATE TABLE emp_audit ( emp_audit_id NUMBER(6), up_date DATE,
new_sal NUMBER(8,2), old_sal NUMBER(8,2) );
CREATE OR REPLACE TRIGGER audit_sal
AFTER UPDATE OF salary ON employees FOR EACH ROW
BEGIN
-- bind variables are used here for values
INSERT INTO emp_audit VALUES( :old.employee_id, SYSDATE,
:new.salary, :old.salary );
END;
/

The executable part of a trigger can contain procedural statements as well as SQL data manipulation statements. Besides table-level triggers, there are instead-of triggers for views and system-event triggers for schemas.

A trigger in Oracle PL/SQL is a named database object that automatically executes a set of SQL or PL/SQL statements when a specific event occurs in the database. Here's a brief overview:

• Event Types:
  • Triggers can be activated by DML (Data Manipulation Language) operations like INSERT, UPDATE, or DELETE on a table or view, or by DDL (Data Definition Language) events like CREATE, ALTER, or DROP on schema objects.
• Trigger Types:
  • Row-level triggers: Fire once for each row affected by the triggering statement.
  • Statement-level triggers: Fire once for the entire SQL statement, regardless of how many rows are affected.
• Timing:
  • BEFORE: Executes before the triggering event happens.
  • AFTER: Executes after the triggering event completes.
  • INSTEAD OF: Used with views to execute alternative SQL statements when the view cannot directly handle DML operations.
• Firing Sequence:
  • For BEFORE triggers, the sequence follows the order in which triggers are created.
  • For AFTER triggers, the sequence is reversed from the creation order.

Here's a simple example of how you might define a trigger:

CREATE OR REPLACE TRIGGER log_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log (action, employee_id, log_time)
    VALUES ('INSERT', :new.employee_id, SYSDATE);
END;
/

This trigger logs an entry into an `employee_log` table every time a new record is inserted into the `employees` table. `:new` refers to the new row's data being inserted. Triggers are powerful for enforcing complex business rules, maintaining referential integrity, or logging changes, but they should be used judiciously because they can impact performance if not managed properly.