BEFORE INSERT Trigger FAQS

1. What is the purpose of a BEFORE INSERT trigger?

A BEFORE INSERT trigger is used to execute logic before a new row is inserted into a table. It can be used for:

• Validating data before insertion.
• Modifying or assigning default values.
• Implementing business rules like uniqueness checks, conditional logic, or setting audit fields (e.g., timestamps, user info).

2. How do I access the new data being inserted in a BEFORE INSERT trigger?

In a BEFORE INSERT trigger, you can access the data that is about to be inserted using the :NEW pseudo-record. The :NEW record holds the new values that will be inserted into the table. For example:

:NEW.column_name

You can modify the value in the :NEW record before it is inserted into the table.

3. Can I change the values being inserted in a BEFORE INSERT trigger?

Yes, you can modify the values being inserted by referencing and modifying the :NEW pseudo-record. For example:

CREATE OR REPLACE TRIGGER set_default_values

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

  IF :NEW.salary IS NULL THEN

    :NEW.salary := 3000;  -- Default value for salary

  END IF;

END;

This will set a default salary if no salary is provided.

4. What is the difference between :NEW and :OLD in a trigger?

• :NEW: Refers to the new values that are being inserted, updated, or deleted. In a BEFORE INSERT trigger, you modify values in :NEW before they are written to the table.
• :OLD: Refers to the old values before an update or delete operation. In BEFORE INSERT triggers, :OLD does not apply since no previous values exist for a new record.

5. What happens if I raise an error in a BEFORE INSERT trigger?

If you raise an error using RAISE_APPLICATION_ERROR or any other exception in a BEFORE INSERT trigger, the insert operation will be aborted, and the error message will be returned to the application.
Example:

RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 1000');

This will stop the insert if the salary is less than 1000.

6. Can I use a BEFORE INSERT trigger to enforce data integrity?

Yes, BEFORE INSERT triggers are commonly used to enforce data integrity by:

• Ensuring that certain fields meet validation criteria.
• Setting default values for missing or NULL data.
• Preventing inserts that violate business rules.
  For example, ensuring a non-negative salary:

CREATE OR REPLACE TRIGGER check_salary

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

  IF :NEW.salary < 0 THEN

    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');

  END IF;

END;

7. Can I call other procedures or functions within a BEFORE INSERT trigger?

Yes, you can call other procedures or functions within a BEFORE INSERT trigger. This is useful for centralizing logic or reusing code across multiple triggers or tables.
Example:

CREATE OR REPLACE TRIGGER set_user_info

BEFORE INSERT ON orders

FOR EACH ROW

BEGIN

  :NEW.created_by := get_current_user();  -- Call a function to get the user

END;

8. Can a BEFORE INSERT trigger be used to prevent inserts?

Yes, you can prevent an insert operation by raising an exception in the trigger. This is useful if you want to prevent data from being inserted based on certain conditions (e.g., violating business rules).
Example:

CREATE OR REPLACE TRIGGER prevent_insert

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

  IF :NEW.salary < 1000 THEN

    RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 1000');

  END IF;

END;

9. Can I modify multiple rows with a single BEFORE INSERT trigger?

A BEFORE INSERT trigger is typically row-level (i.e., it fires for each row inserted into the table). If you are inserting multiple rows, the trigger will fire once for each row, and you can modify the individual rows using the :NEW pseudo-record.
However, if you need to perform actions on all rows at once, you might need a statement-level trigger (although these are less common with insert operations).

10. Can I execute DML operations (INSERT, UPDATE, DELETE) in a BEFORE INSERT trigger?

Technically, you can execute DML operations in a BEFORE INSERT trigger, but it is not recommended. Doing so can lead to unintended consequences such as:

• Mutating table errors.
• Performance problems.
  If you need to perform DML operations in response to an insert, consider using a AFTER INSERT trigger instead.

11. What is a "mutating table" error, and how do I avoid it?

A mutating table error occurs when a trigger tries to modify or query the table that is causing the trigger to fire. Oracle prevents this to avoid logical inconsistencies or infinite loops.
To avoid a mutating table error:

• Avoid modifying or querying the same table within a BEFORE INSERT trigger.
• Consider using statement-level triggers or auxiliary tables to store intermediate results.

12. How can I disable a BEFORE INSERT trigger temporarily?

You can disable a trigger temporarily with the following command:

ALTER TRIGGER trigger_name DISABLE;

This is useful when you need to perform bulk operations and don't want the trigger to fire.

13. Can a BEFORE INSERT trigger be fired for bulk inserts (multiple rows)?

Yes, a BEFORE INSERT trigger will fire for each individual row in a bulk insert operation. However, be cautious when designing triggers for bulk operations, as they can slow down performance.
To improve performance, you might consider minimizing the logic in the trigger or disabling it temporarily during large insert operations.

14. Can I use a BEFORE INSERT trigger to automatically generate a primary key?

Yes, you can use a BEFORE INSERT trigger to generate a primary key value, such as using a sequence, before the row is inserted. For example:

CREATE OR REPLACE TRIGGER auto_generate_id

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

  :NEW.employee_id := employees_seq.NEXTVAL;  -- Assign a new value from the sequence

END;

This ensures that the employee_id is automatically generated before the insert.

15. How can I test a BEFORE INSERT trigger?

You can test a BEFORE INSERT trigger by performing an insert operation on the table that the trigger is associated with. For example:

INSERT INTO employees (employee_name, salary)

VALUES ('John Doe', 500);

If the trigger has validation logic (e.g., raising an error for a salary less than a certain amount), the insert will fail if the condition is violated.