trigger

 

Here is the same information you requested, displayed for better readability:

1. What is an Oracle trigger?

  • An Oracle trigger is a database object that automatically executes or fires when certain events occur in the database.
  • Triggers are used to enforce business rules, validate data, or automate system tasks.
  • They can be defined to run before or after a specific DML (Data Manipulation Language) operation, such as INSERT, UPDATE, or DELETE, or on specific database events like logging in or logging out.

2. How does an Oracle trigger work?

An Oracle trigger works by associating it with a table, view, or schema. When a specific trigger event (like an INSERT, UPDATE, or DELETE operation) occurs on the associated object, the trigger is fired automatically. It is part of the transaction that caused the trigger to fire, and depending on its type, it can be executed before or after the event or in place of the event.

For example:

  • When a record is inserted into a table, a trigger can be defined to automatically validate the data before it gets inserted.
  • When a record is updated, a trigger can log the changes for auditing purposes.

3. What are the different types of Oracle triggers?

There are several types of triggers in Oracle:

  • DML Triggers: These are fired in response to data modification events like INSERT, UPDATE, or DELETE.
  • DCL Triggers: These respond to Data Control Language operations, such as GRANT and REVOKE.
  • DDL Triggers: These respond to Data Definition Language events like CREATE, ALTER, or DROP commands.
  • LOGON and LOGOFF Triggers: Fired when a user logs in or logs off.
  • INSTEAD OF Triggers: These are used to define a trigger that performs an action instead of the event (commonly used on views).

4. What is a BEFORE trigger in Oracle?

A BEFORE trigger is fired before the execution of the triggering event (like an INSERT, UPDATE, or DELETE). For example, you might use a BEFORE trigger to validate or modify data before it is inserted or updated in the database.

Example:

CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
    END IF;
END;

This trigger checks if the salary being inserted is negative before the insert happens and raises an error if true.

5. What is an AFTER trigger in Oracle?

An AFTER trigger is fired after the execution of the triggering event (INSERT, UPDATE, DELETE). After the event is successfully completed, the trigger will execute. You might use an AFTER trigger to perform actions such as logging or sending notifications.

Example:

CREATE OR REPLACE TRIGGER after_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (user_action, action_time)
    VALUES ('Employee Inserted', SYSDATE);
END;

This trigger inserts a record into an audit log after a new employee is inserted into the "employees" table.

6. What is an INSTEAD OF trigger in Oracle?

An INSTEAD OF trigger is used to perform an operation instead of the one that caused the trigger to fire. These are mainly used on views. For example, when performing an INSERT on a view, instead of inserting data directly into the view, you can redirect the operation to one or more underlying tables.

Example:

CREATE OR REPLACE TRIGGER instead_of_trigger
INSTEAD OF INSERT ON employee_view
FOR EACH ROW
BEGIN
    INSERT INTO employees (emp_id, emp_name) VALUES (:NEW.emp_id, :NEW.emp_name);
    INSERT INTO salaries (emp_id, salary) VALUES (:NEW.emp_id, :NEW.salary);
END;

This trigger inserts data into multiple tables instead of performing the insert directly on the view.

7. How do you create a trigger in Oracle?

You create a trigger in Oracle using the CREATE TRIGGER statement. Here's a basic syntax:

CREATE [OR REPLACE] TRIGGER trigger_name
trigger_time trigger_event
ON table_name
[FOR EACH ROW]
BEGIN
    -- Trigger logic
END;

Where:

  • trigger_time is either BEFORE, AFTER, or INSTEAD OF.
  • trigger_event is the event (INSERT, UPDATE, DELETE).
  • table_name is the table or view associated with the trigger.

8. What is a trigger body in Oracle?

The trigger body is the block of PL/SQL code that defines the actions to be performed when the trigger is fired. It contains logic that will be executed automatically when the specified event occurs.

For example:

CREATE OR REPLACE TRIGGER check_salary
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary > 10000 THEN
        DBMS_OUTPUT.PUT_LINE('High salary!');
    END IF;
END;

In this example, the trigger body contains simple logic to print a message if the salary inserted is above 10,000.

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

  • A BEFORE trigger executes before the triggering event (e.g., before data is inserted into a table). It can be used for data validation or modification.
  • An AFTER trigger executes after the event (e.g., after data is inserted into a table). It is useful for auditing, logging, or cascading operations that depend on the completed action.

10. What is a DML trigger in Oracle?

A DML (Data Manipulation Language) trigger is a trigger that is associated with data modification operations, such as INSERT, UPDATE, or DELETE. These triggers help enforce business rules or maintain data integrity.

Example:

CREATE OR REPLACE TRIGGER update_employee_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be decreased');
    END IF;
END;

This trigger prevents a decrease in an employee's salary during an update.

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

DML triggers are categorized as:

  • BEFORE triggers: Executes before a DML event (INSERT, UPDATE, DELETE).
  • AFTER triggers: Executes after a DML event (INSERT, UPDATE, DELETE).
  • INSTEAD OF triggers: Executes instead of a DML event, typically used with views.

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

A statement-level trigger fires once for the entire DML statement, regardless of how many rows are affected. It does not use FOR EACH ROW. This type of trigger is often used for logging or auditing purposes.

Example:

CREATE OR REPLACE TRIGGER statement_trigger
AFTER DELETE ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('Employee deleted.');
END;

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

A row-level trigger executes once for each row affected by the DML operation (e.g., if 10 rows are updated, the trigger will fire 10 times). It uses the FOR EACH ROW clause.

Example:

CREATE OR REPLACE TRIGGER row_trigger
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    DBMS_OUTPUT.PUT_LINE('Deleted employee: ' || :OLD.emp_name);
END;

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

Yes, triggers can be used for database auditing in Oracle. You can create triggers to log changes, such as INSERT, UPDATE, or DELETE operations on important tables, to an audit table for tracking and compliance.

Example:

CREATE OR REPLACE TRIGGER audit_employee_changes
AFTER INSERT OR DELETE OR UPDATE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO audit_log (action, emp_id, action_time)
    VALUES (CASE
                WHEN INSERTING THEN 'INSERT'
                WHEN DELETING THEN 'DELETE'
                WHEN UPDATING THEN 'UPDATE'
            END, :NEW.emp_id, SYSDATE);
END;

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

Triggers in Oracle are used for a variety of purposes, including:

  • Enforcing business rules
  • Automating system tasks
  • Maintaining data integrity
  • Auditing data changes
  • Performing data validation before or after DML operations

16. Can a trigger be disabled in Oracle?

Yes, you can disable a trigger in Oracle using the ALTER TRIGGER command. For example:

ALTER TRIGGER trigger_name DISABLE;

17. How can a trigger be dropped in Oracle?

You can drop a trigger using the DROP TRIGGER statement:

DROP TRIGGER trigger_name;

18. How do you enable a trigger in Oracle?

You can enable a trigger in Oracle using the ALTER TRIGGER command:

ALTER TRIGGER trigger_name ENABLE;

19. How do you modify a trigger in Oracle?

To modify a trigger in Oracle, you can either drop and recreate the trigger or use the CREATE OR REPLACE statement to redefine the trigger. You cannot modify a trigger directly with an ALTER command.

Example:

CREATE OR REPLACE TRIGGER trigger_name
AFTER INSERT ON table_name
FOR EACH ROW
BEGIN
    -- new logic
END;

20. What is a trigger event in Oracle?

A trigger event is the action that causes the trigger to fire. It can be a DML operation (INSERT, UPDATE, DELETE), a DDL operation (CREATE, DROP), a system event (LOGON, LOGOFF), or any other predefined action.

Let me know if you'd like any further explanation on any of the points!

No comments:

Post a Comment

Subscribe to: Post Comments (Atom)