21. Can a trigger be called multiple times in Oracle?
Yes, a trigger can be fired multiple times, depending on the operation performed. If the trigger is associated with an event like INSERT, UPDATE, or DELETE, it will execute every time that specific event occurs on the associated table or view. For example, if an INSERT trigger is defined on a table, it will fire each time a new row is inserted into the table.
Example:
CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('New row inserted');
END;
Every time a row is inserted into my_table, this trigger will execute and print "New row inserted".
22. What is the maximum number of triggers you can have on a table in Oracle?
Oracle allows up to 12 triggers per table for each triggering event. These can be a combination of different timing (BEFORE, AFTER) and events (INSERT, UPDATE, DELETE). However, if there is a need to handle more than 12 triggers, you may need to rethink your design.
23. Can a trigger call another trigger in Oracle?
Triggers themselves cannot directly "call" another trigger. However, a trigger can execute a statement that indirectly causes another trigger to fire. For example, an INSERT trigger can modify a table in such a way that another INSERT or UPDATE trigger is invoked as a result.
24. What are trigger variables in Oracle?
Trigger variables in Oracle are special variables that store information about the data being manipulated when the trigger fires. These include the NEW and OLD values, which store the new and old values of columns affected by INSERT, UPDATE, and DELETE operations.
25. What are the NEW and OLD trigger variables in Oracle?
- NEW: Holds the new value of a column for INSERT and UPDATE operations. It is used to reference the values that are about to be inserted or updated.
- OLD: Holds the old value of a column for UPDATE and DELETE operations. It is used to reference the values before the update or deletion occurs.
Example for UPDATE:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Old value: ' || :OLD.column_name);
DBMS_OUTPUT.PUT_LINE('New value: ' || :NEW.column_name);
END;
This will print the old and new values of column_name whenever an update is performed.
26. What are the benefits of using triggers in Oracle?
- Data integrity: Triggers help enforce business rules automatically when data is inserted, updated, or deleted.
- Auditing: Triggers can automatically log changes to data, ensuring an audit trail.
- Automation: Triggers can automate tasks like updating timestamps, maintaining logs, or cascading changes.
- Security: Triggers can be used to validate data before it is committed to the database.
27. What is a compound trigger in Oracle?
A compound trigger is a type of trigger in Oracle that allows you to combine multiple triggers into one. This is particularly useful when you want to handle multiple BEFORE and AFTER events in a single trigger. It provides a way to handle different parts of the triggering logic in a single place and can help avoid issues with the trigger firing sequence.
Example:
CREATE OR REPLACE TRIGGER compound_trigger_example
FOR INSERT OR UPDATE OR DELETE ON my_table
COMPOUND TRIGGER
BEFORE STATEMENT IS
BEGIN
-- Statement-level logic
END BEFORE STATEMENT;
AFTER EACH ROW IS
BEGIN
-- Row-level logic
END AFTER EACH ROW;
END compound_trigger_example;
28. What are the disadvantages of using triggers in Oracle?
- Complexity: Triggers can make the logic of the database more complex and harder to maintain.
- Performance: Triggers can impact performance, especially if they are called frequently or involve complex logic.
- Unintended consequences: A trigger might execute operations that result in unintended side effects, such as cascading updates or inserts.
- Debugging: Debugging triggers can be difficult because they are automatically fired and can lead to unexpected results if not properly managed.
29. How can you prevent a trigger from executing in Oracle?
You can disable a trigger to prevent it from executing. This can be done using the ALTER TRIGGER command:
ALTER TRIGGER trigger_name DISABLE;
To re-enable the trigger, use:
ALTER TRIGGER trigger_name ENABLE;
30. What is a database trigger in Oracle?
A database trigger in Oracle is a stored procedure that automatically executes (or "fires") in response to a specific event (like an INSERT, UPDATE, or DELETE) on a table or view. Triggers help automate system operations and enforce business rules.
31. What is an INSTEAD OF trigger used for in Oracle?
An INSTEAD OF trigger is used to perform an action instead of the default action for the INSERT, UPDATE, or DELETE operation on a view. This is typically used when you want to modify data through a view and need to perform different actions behind the scenes.
Example:
CREATE OR REPLACE TRIGGER my_instead_of_trigger
INSTEAD OF INSERT ON my_view
FOR EACH ROW
BEGIN
INSERT INTO my_table (column1, column2) VALUES (:NEW.column1, :NEW.column2);
END;
This allows you to insert into a view and have it map to an underlying table instead.
32. Can triggers be nested in Oracle?
Yes, triggers can be nested in Oracle, meaning that one trigger can cause another trigger to fire. However, this can lead to circular references and cause complications if not carefully managed. You can control the nesting depth using PRAGMA directives to prevent infinite loops.
33. How can you handle errors in an Oracle trigger?
You can use exception handling to manage errors within a trigger. This can be done using EXCEPTION blocks, similar to how exceptions are handled in stored procedures.
Example:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
-- Trigger logic here
EXCEPTION
WHEN OTHERS THEN
-- Error handling logic
DBMS_OUTPUT.PUT_LINE('Error occurred: ' || SQLERRM);
END;
34. What is a trigger firing sequence in Oracle?
When multiple triggers are defined on a table for the same event, Oracle determines the order in which they are fired based on their timing (BEFORE, AFTER) and type (ROW-level or STATEMENT-level). You can specify the firing order explicitly with the FIRE clause when defining triggers.
35. Can you execute a function from a trigger in Oracle?
Yes, you can execute a function within a trigger in Oracle. A function call can be included in the trigger body just like any other SQL or PL/SQL statement.
Example:
CREATE OR REPLACE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
my_function(:NEW.column_name);
END;
36. What happens if a trigger fails in Oracle?
If a trigger fails (due to an error in the trigger body), Oracle will roll back the associated operation (INSERT, UPDATE, DELETE) and the error is raised. You can use the EXCEPTION block to catch and handle errors gracefully.
37. How do you handle exceptions in an Oracle trigger?
Exceptions in an Oracle trigger can be handled using the EXCEPTION section, where you can define actions to take in case of an error. For example, you could log the error or raise a custom exception.
38. Can a trigger modify the data in Oracle?
Yes, a trigger can modify the data. For example, a BEFORE INSERT trigger can modify the values of the row before it is inserted into the table. Similarly, AFTER UPDATE triggers can modify data after the update operation.
39. Can triggers be used with views in Oracle?
Yes, triggers can be created on views in Oracle. An INSTEAD OF trigger is typically used for this purpose. It allows the trigger to replace the default behavior of an INSERT, UPDATE, or DELETE operation on the view.
40. What is the difference between a trigger and a stored procedure in Oracle?
- Triggers: Automatically executed in response to specific events (INSERT, UPDATE, DELETE) on a table or view. They cannot be manually called.
- Stored Procedures: Can be explicitly invoked by the user or application code. They are not automatically triggered by database events.
Example: A stored procedure can be called explicitly:
EXEC my_procedure;
A trigger fires automatically when the corresponding event occurs on a table:
CREATE TRIGGER my_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
-- Trigger logic
END;
No comments:
Post a Comment