Oracle Trigger - 5

81. How do you add new triggers to a schema in Oracle?

To add a new trigger to a schema, you simply create the trigger using the CREATE TRIGGER statement. The trigger can be added to a specific table, view, or schema object depending on the use case. The trigger definition must specify the event (INSERT, UPDATE, DELETE), the timing (BEFORE, AFTER), and whether it is row-level or statement-level.

Example:

CREATE OR REPLACE TRIGGER my_trigger

AFTER INSERT ON my_table

FOR EACH ROW

BEGIN

  -- Trigger logic goes here

  DBMS_OUTPUT.PUT_LINE('New row inserted');

END;

This adds a trigger my_trigger to the my_table in the schema.

82. How do you handle updates made by triggers in Oracle?

When you use a trigger for an update operation, you can modify the values of the columns in the :NEW pseudo-record (for row-level triggers). If a trigger modifies data, it can lead to cascading effects, so it's important to ensure that the updates done by the trigger don't conflict with the business logic or introduce recursive behavior.

Example (modifying the salary column):

CREATE OR REPLACE TRIGGER update_salary

BEFORE UPDATE ON employees

FOR EACH ROW

BEGIN

  -- Update the salary column if the new salary is less than the old salary

  IF :NEW.salary < :OLD.salary THEN

    :NEW.salary := :OLD.salary;  -- Prevent salary reduction

  END IF;

END;

This trigger ensures that the salary cannot be decreased by an update.

83. What is the maximum size of a trigger in Oracle?

The maximum size of a trigger in Oracle is governed by the size of PL/SQL code that can be executed in the trigger. The maximum size for a trigger’s PL/SQL block is 32 KB of source code. This includes the entire PL/SQL block, including declarations, logic, and exception handling. If your trigger logic exceeds this size, you might need to break it into multiple smaller triggers or procedures.

84. How does Oracle handle transaction management with triggers?

Oracle triggers execute within the context of the transaction that caused them to fire. For example:

• If a DML operation (like INSERT, UPDATE, DELETE) is rolled back, the changes made by the trigger are also rolled back.
• If the trigger raises an exception, the transaction is aborted, and the changes made by the trigger and the original DML operation are rolled back.

Oracle's transaction management ensures that the trigger execution is atomic and is part of the same transaction as the triggering operation.

Example:

CREATE OR REPLACE TRIGGER transaction_trigger

AFTER INSERT ON orders

FOR EACH ROW

BEGIN

  -- Insert into audit table (part of the same transaction)

  INSERT INTO order_audit (order_id, action_time) VALUES (:NEW.order_id, SYSDATE);

END;

If the INSERT on the orders table is rolled back, the audit table insert is also rolled back.

85. What is the significance of the "WHEN" clause in Oracle triggers?

The WHEN clause in Oracle triggers allows you to specify a condition that must be true for the trigger to fire. This condition is evaluated at runtime, and if the condition is false, the trigger will not fire.

Example:

CREATE OR REPLACE TRIGGER when_clause_example

AFTER INSERT ON my_table

FOR EACH ROW

WHEN (NEW.salary > 10000)

BEGIN

  -- Trigger logic only runs if the salary is greater than 10000

  DBMS_OUTPUT.PUT_LINE('High salary inserted');

END;

In this example, the trigger only fires if the inserted salary is greater than 10,000.

86. How do you prevent triggers from affecting business logic in Oracle?

To prevent triggers from affecting business logic:

1. Ensure the trigger logic is decoupled from critical business logic, and only performs non-critical operations like auditing or logging.
2. Use conditional logic in triggers (e.g., WHEN clause or IF statements) to limit the impact on business logic.
3. Avoid recursive triggers by controlling the firing sequence or using flags to disable triggers temporarily when needed.

87. What is the impact of triggers on concurrency in Oracle?

Triggers can impact concurrency by introducing additional overhead during DML operations. Since triggers execute within the same transaction as the DML operation, they can lock additional resources, slow down the execution of the transaction, or create contention for database resources. For example:

• Row-level triggers can increase the load when many rows are affected.
• Statement-level triggers impact concurrency less but still add overhead for large operations.

Solution: To mitigate concurrency issues, keep trigger logic efficient, avoid complex operations within triggers, and monitor performance closely.

88. Can triggers be used to audit user actions in Oracle?

Yes, triggers can be used for auditing user actions by capturing the details of INSERT, UPDATE, or DELETE operations in audit tables. You can log the user, timestamp, and type of operation, as well as the old and new values.

Example (auditing user changes):

CREATE OR REPLACE TRIGGER audit_user_changes

AFTER INSERT OR UPDATE OR DELETE ON employees

FOR EACH ROW

BEGIN

  INSERT INTO audit_log (user_name, action_time, action_type, old_salary, new_salary)

  VALUES (USER, SYSDATE,

    CASE WHEN INSERTING THEN 'INSERT' WHEN UPDATING THEN 'UPDATE' WHEN DELETING THEN 'DELETE' END,

    :OLD.salary, :NEW.salary);

END;

This captures any changes made to the employees table along with the user and the action type.

89. Can triggers be used to enforce referential integrity in Oracle?

Yes, triggers can be used to enforce referential integrity in cases where you need custom logic for cascading actions, such as updating or deleting related rows in child tables when a row in the parent table is updated or deleted.

Example (cascade delete):

CREATE OR REPLACE TRIGGER cascade_delete

AFTER DELETE ON parent_table

FOR EACH ROW

BEGIN

  DELETE FROM child_table WHERE parent_id = :OLD.parent_id;

END;

This trigger ensures that when a row is deleted from the parent_table, corresponding rows in the child_table are also deleted.

90. How do you manage triggers in large Oracle databases?

In large databases, managing triggers can become challenging. Here are some tips:

• Naming conventions: Use consistent naming conventions for triggers to make them easier to identify and manage.
• Documentation: Maintain documentation that details the triggers' purpose, actions, and dependencies.
• Performance monitoring: Regularly monitor trigger performance, especially for large DML operations.
• Group triggers: Use compound triggers to manage related actions and reduce the number of separate triggers.
• Testing: Test triggers thoroughly in development environments before deploying them to production to ensure they don't interfere with business logic.

91. What is the difference between BEFORE and AFTER trigger firing sequences in Oracle?

• BEFORE triggers fire before the DML operation (INSERT, UPDATE, DELETE) is applied to the table. This allows the trigger to modify data before it's committed, making it useful for data validation or transformation.

Example:

CREATE OR REPLACE TRIGGER before_insert_trigger

BEFORE INSERT ON my_table

FOR EACH ROW

BEGIN

  :NEW.created_at := SYSDATE;

END;

• AFTER triggers fire after the DML operation is committed to the table. This allows the trigger to perform actions such as logging, auditing, or updating other tables after the primary operation.

Example:

CREATE OR REPLACE TRIGGER after_insert_trigger

AFTER INSERT ON my_table

FOR EACH ROW

BEGIN

  -- Perform post-insert actions, such as auditing

  INSERT INTO audit_log (message) VALUES ('Row inserted');

END;

92. How does an INSTEAD OF trigger work in Oracle views?

An INSTEAD OF trigger is used on views to perform DML operations (INSERT, UPDATE, DELETE) on the underlying base tables when a DML operation is attempted on the view. This is useful because views are typically read-only, but you can define custom behavior for modifying data in a view using INSTEAD OF triggers.

Example:

CREATE OR REPLACE TRIGGER instead_of_trigger

INSTEAD OF INSERT ON my_view

FOR EACH ROW

BEGIN

  -- Perform an insert on the underlying table

  INSERT INTO my_table (column1, column2)

  VALUES (:NEW.column1, :NEW.column2);

END;

In this case, the trigger takes the INSERT on the view and executes an INSERT on the underlying table.

93. Can triggers be used in Oracle to maintain data consistency?

Yes, triggers are often used to maintain data consistency. They can ensure that related data in multiple tables remains consistent during DML operations. For example, triggers can:

• Ensure values are synchronized between parent and child tables.
• Automatically update audit tables whenever data changes.
• Enforce business rules that require multiple tables to be in sync.

94. What are the performance implications of using triggers in Oracle?

Triggers introduce some performance overhead because they add additional processing during DML operations. The impact can be significant if:

• The trigger performs complex operations or calls other procedures.
• The trigger is row-level and many rows are affected.
• There is nested or recursive triggering.

To mitigate performance issues:

• Keep trigger logic simple.
• Use statement-level triggers when possible.
• Avoid DML operations within triggers unless absolutely necessary.

95. How do you write a complex Oracle trigger?

To write a complex trigger, you need to plan the trigger logic carefully, potentially involving multiple operations. Consider using compound triggers to encapsulate multiple actions and avoid conflicts. Also, break down the trigger into smaller, modular pieces (such as calling stored procedures) to improve readability and maintainability.

Example (complex trigger):

CREATE OR REPLACE TRIGGER complex_trigger

AFTER INSERT OR UPDATE ON orders

FOR EACH ROW

DECLARE

  v_discount NUMBER;

BEGIN

  -- Calculate discount

  SELECT discount INTO v_discount

  FROM customers

  WHERE customer_id = :NEW.customer_id;

 

  -- Update order with calculated discount

  :NEW.discount := v_discount;

 

  -- Log the action

  INSERT INTO audit_log (action_type, order_id, action_time)

  VALUES ('UPDATE', :NEW.order_id, SYSDATE);

END;

96. Can triggers be used in Oracle to update audit tables?

Yes, triggers are commonly used to update audit tables. By inserting details such as the type of operation, timestamp, and affected rows, you can create a complete audit trail of changes in your database.

Example (updating an audit table):

CREATE OR REPLACE TRIGGER audit_trigger

AFTER UPDATE ON employees

FOR EACH ROW

BEGIN

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

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

END;

97. Can you use a trigger to call external procedures or APIs in Oracle?

Yes, you can use triggers to call external procedures or APIs by leveraging external procedures or web services. You can invoke stored procedures that interact with external systems, call web services, or even trigger asynchronous jobs from within a trigger.

Example (calling an external procedure):

CREATE OR REPLACE TRIGGER external_procedure_trigger

AFTER INSERT ON orders

FOR EACH ROW

BEGIN

  -- Call external procedure

  EXECUTE IMMEDIATE 'BEGIN external_procedure(:NEW.order_id); END;';

END;

98. How do you troubleshoot trigger-related issues in Oracle?

To troubleshoot trigger-related issues:

1. Use DBMS_OUTPUT to print debug messages in your triggers.
2. Check the trigger execution order: Look at the order of triggers and potential conflicts between them.
3. Check exceptions: Look for any unhandled exceptions within the trigger code using EXCEPTION blocks.
4. Enable SQL trace: Use DBMS_SESSION.set_sql_trace to trace SQL statements executed by triggers.
5. Use logging tables: Log trigger actions and exceptions into separate tables for easier debugging.

99. How do you optimize triggers for better performance in Oracle?

To optimize triggers:

1. Keep the logic inside the trigger as simple as possible.
2. Use statement-level triggers when applicable to avoid row-level operations.
3. Minimize DML operations inside triggers.
4. Use bulk processing (e.g., BULK COLLECT, FORALL) for large volumes of rows.
5. Monitor trigger execution and tune the database performance regularly.

100. Can you create triggers on system tables in Oracle?

You cannot create triggers directly on most system tables in Oracle. Oracle restricts the creation of triggers on internal, read-only tables to maintain the integrity of the database's core functionality. However, you can create triggers on user-defined tables or views to monitor changes made to application data.