Oracle Trigger - 1

1. What is an Oracle trigger?

• An Oracle trigger is a set of SQL statements stored in the database that automatically execute or fire when a specific event occurs in the database.
• Triggers are often used for enforcing business rules, data validation, logging, and auditing purposes.
• They are associated with a specific table or view and are automatically executed in response to specific events like INSERT, UPDATE, or DELETE operations.

Example:

• A trigger might automatically update an audit table whenever a record in the employee table is deleted.

2. How does an Oracle trigger work?

• Oracle triggers are activated based on specific database events like an INSERT, UPDATE, or DELETE statement.
• When the triggering event occurs, the trigger’s associated code (which can be PL/SQL) is automatically executed.

For example:

• Row-level trigger: Executes for each row affected by the event (e.g., for every row inserted into a table).
• Statement-level trigger: Executes once for the entire SQL statement, regardless of how many rows are affected.

3. What are the different types of Oracle triggers?

• Oracle triggers are categorized based on when they execute and what kind of event they respond to. 

The different types are:

1. DML (Data Manipulation Language) Triggers: Triggered by INSERT, UPDATE, DELETE statements.
2. DDL (Data Definition Language) Triggers: Triggered by data definition events like CREATE, ALTER, DROP.
3. LOGON/LOGOFF Triggers: Triggered when a user logs into or logs off from the database.
4. Compound Triggers: Allows you to combine multiple actions that are executed together within a single trigger.
5. INSTEAD OF Triggers: Used to perform operations like INSERT, UPDATE, DELETE on views.

4. What is a BEFORE trigger in Oracle?

• A BEFORE trigger is a type of trigger that is fired before the DML event (INSERT, UPDATE, DELETE) takes place.
• It allows you to modify or validate data before the changes are actually made to the database.

Example:

• A BEFORE INSERT trigger can be used to set default values for certain columns before the record is inserted.

CREATE OR REPLACE TRIGGER before_employee_insert

BEFORE INSERT ON employees

FOR EACH ROW

BEGIN

  IF :NEW.department_id IS NULL THEN

    :NEW.department_id := 10;

  END IF;

END;

• This trigger ensures that every time a record is inserted into the employees table without a department ID, it will automatically assign the department ID 10.

5. What is an AFTER trigger in Oracle?

• An AFTER trigger is a type of trigger that fires after the DML event (INSERT, UPDATE, DELETE) has been completed.
• It is typically used for auditing, logging, or cascading actions (such as updating related records).

Example:
An AFTER UPDATE trigger can be used to track changes made to a specific column in a table.

CREATE OR REPLACE TRIGGER after_employee_update

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO employee_audit (employee_id, action_time, old_salary, new_salary)

  VALUES (:OLD.employee_id, SYSDATE, :OLD.salary, :NEW.salary);

END;

• This trigger inserts a record into the employee_audit table whenever an employee’s salary is updated.

6. What is an INSTEAD OF trigger in Oracle?

• An INSTEAD OF trigger is used to perform actions like INSERT, UPDATE, or DELETE on views, instead of performing the action directly on the view. 
• This is especially useful for views that are not directly updatable.

Example:

• If you have a view that combines data from multiple tables, you can create an INSTEAD OF trigger to perform an action on the underlying tables when a user tries to insert data into the view.

CREATE OR REPLACE TRIGGER instead_of_employee_insert

INSTEAD OF INSERT ON employee_view

FOR EACH ROW

BEGIN

  INSERT INTO employees (employee_id, name, salary)

  VALUES (:NEW.employee_id, :NEW.name, :NEW.salary);

END;

• This trigger intercepts insert operations on the employee_view and inserts the data into the employees table instead.

7. How do you create a trigger in Oracle?

• To create a trigger in Oracle, you use the CREATE TRIGGER statement. 
• You define the triggering event (INSERT, UPDATE, DELETE), the timing (BEFORE, AFTER), and the action to be performed.

Syntax:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER | INSTEAD OF} {INSERT | UPDATE | DELETE}

ON table_name

[FOR EACH ROW]

[WHEN (condition)]

DECLARE

  -- Variable declarations

BEGIN

  -- Trigger body

END;

Example:

CREATE OR REPLACE TRIGGER my_trigger

AFTER INSERT ON my_table

FOR EACH ROW

BEGIN

  -- Trigger body goes here

END;

8. What is a trigger body in Oracle?

• The trigger body contains the actual code that is executed when the trigger is fired.
• It can contain PL/SQL logic to perform various actions such as modifying data, raising exceptions, logging changes, etc.

Example:

BEGIN

  INSERT INTO log_table (operation, timestamp)

  VALUES ('INSERT', SYSDATE);

END;

• This code inside the trigger body records the operation (insert) along with the timestamp whenever the trigger fires.

9. What is the difference between a BEFORE and AFTER trigger in Oracle?

• BEFORE Trigger: Fires before the DML operation takes place. It is used for validation or modification of data before it is committed to the database.
• AFTER Trigger: Fires after the DML operation completes. It is used for actions like logging, auditing, or performing cascading changes.

10. What is a DML trigger in Oracle?

• A DML trigger is a type of trigger that is fired in response to a DML statement such as INSERT, UPDATE, or DELETE.
• These triggers can be set to fire before or after the data modification occurs.

11. What are the different types of DML triggers in Oracle?

 BEFORE DML Triggers: Executed before a DML operation (INSERT, UPDATE, DELETE).

 AFTER DML Triggers: 

• Executed after a DML operation.

 INSTEAD OF DML Triggers: 

• Used for views to perform DML actions on underlying tables instead of directly on the view.

12. What is a statement-level trigger in Oracle?

• A statement-level trigger is fired once for the entire SQL statement, regardless of how many rows are affected.
• It is useful for actions that need to happen only once for a specific event.

Example:

CREATE OR REPLACE TRIGGER statement_level_trigger

AFTER INSERT ON employees

BEGIN

  INSERT INTO audit_log (message) VALUES ('Employees table updated');

END;

13. What is a row-level trigger in Oracle?

• A row-level trigger is fired once for each row affected by the DML statement. It allows the trigger to process each row individually.

Example:

CREATE OR REPLACE TRIGGER row_level_trigger

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO audit_log (message)

  VALUES ('Employee ' || :OLD.employee_id || ' salary changed');

END;

14. Can triggers be used for database auditing in Oracle?

• Yes, triggers can be used for database auditing.
• By creating triggers for DML operations (INSERT, UPDATE, DELETE), you can log changes to a separate audit table, tracking who made the changes, what changes were made, and when they occurred.

15. What is the purpose of a trigger in Oracle?

Triggers are used to automate tasks such as:

• Data validation.
• Enforcing business rules.
• Auditing changes to the database.
• Performing cascading updates or inserts.
• Logging or tracking actions performed on the database.

16. Can a trigger be disabled in Oracle?

• Yes, a trigger can be disabled using the ALTER TRIGGER command.

Example:

ALTER TRIGGER my_trigger DISABLE;

17. How can a trigger be dropped in Oracle?

• To remove a trigger, you can use the DROP TRIGGER command.

Example:

DROP TRIGGER my_trigger;

18. How do you enable a trigger in Oracle?

• To enable a trigger, you use the ALTER TRIGGER command.

Example:

ALTER TRIGGER my_trigger ENABLE;

19. How do you modify a trigger in Oracle?

• To modify a trigger, you must recreate it using the CREATE OR REPLACE TRIGGER statement.
• Oracle does not allow direct modification of triggers.

20. What is a trigger event in Oracle?

• A trigger event is the action or condition that causes the trigger to fire. 
• The most common trigger events are INSERT, UPDATE, and DELETE, but other events like LOGON or LOGOFF are also possible.

Example:

CREATE OR REPLACE TRIGGER my_trigger

AFTER INSERT ON my_table

BEGIN

  -- Trigger body

END;