41. What is a BEFORE INSERT trigger in Oracle?
A BEFORE INSERT trigger is fired before a row is inserted into a table. This trigger allows you to modify or validate data before the actual insert occurs. It is often used to ensure data integrity by enforcing business rules before the data is committed.
Example:
CREATE OR REPLACE TRIGGER before_insert_trigger
BEFORE INSERT ON my_table
FOR EACH ROW
BEGIN
-- Ensure the value of a column is not null before inserting
IF :NEW.column_name IS NULL THEN
RAISE_APPLICATION_ERROR(-20001, 'column_name cannot be NULL');
END IF;
END;
This trigger will prevent an insert if the column_name is NULL.
42. What is an AFTER INSERT trigger in Oracle?
An AFTER INSERT trigger is executed after a row is inserted into a table. It is typically used for operations that need to be performed after the data is inserted, such as logging, cascading actions, or updating other related tables.
Example:
CREATE OR REPLACE TRIGGER after_insert_trigger
AFTER INSERT ON my_table
FOR EACH ROW
BEGIN
-- Log the insertion into an audit table
INSERT INTO audit_table (action, table_name, row_id)
VALUES ('INSERT', 'my_table', :NEW.id);
END;
This trigger logs each insert operation in the audit_table after a new row is inserted into my_table.
43. What is a BEFORE UPDATE trigger in Oracle?
A BEFORE UPDATE trigger is fired before an update is applied to a row. This trigger allows you to validate or modify the data before the update operation takes place, which can be useful for enforcing data integrity or applying business rules.
Example:
CREATE OR REPLACE TRIGGER before_update_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
-- Check if the salary value is increasing before allowing the update
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be decreased');
END IF;
END;
This trigger prevents salary from being decreased during an update.
44. What is an AFTER UPDATE trigger in Oracle?
An AFTER UPDATE trigger is executed after a row is updated. It is useful for performing actions that need to occur once the data modification is completed, such as updating audit logs or cascading updates to related tables.
Example:
CREATE OR REPLACE TRIGGER after_update_trigger
AFTER UPDATE ON my_table
FOR EACH ROW
BEGIN
-- Log the update to an audit table
INSERT INTO audit_table (action, table_name, old_value, new_value)
VALUES ('UPDATE', 'my_table', :OLD.column_name, :NEW.column_name);
END;
This trigger logs changes to the column_name during updates in the audit_table.
45. What is a BEFORE DELETE trigger in Oracle?
A BEFORE DELETE trigger is fired before a row is deleted from a table. It allows you to perform validation or backup the data before it is removed. This type of trigger is often used for enforcing rules like preventing deletion based on certain conditions or archiving data before removal.
Example:
CREATE OR REPLACE TRIGGER before_delete_trigger
BEFORE DELETE ON my_table
FOR EACH ROW
BEGIN
-- Prevent deletion if the record is marked as "active"
IF :OLD.status = 'active' THEN
RAISE_APPLICATION_ERROR(-20003, 'Cannot delete active records');
END IF;
END;
This trigger prevents the deletion of records where the status is 'active'.
46. What is an AFTER DELETE trigger in Oracle?
An AFTER DELETE trigger is executed after a row is deleted from a table. It can be used to clean up related data, update statistics, or log the deletion event to an audit table.
Example:
CREATE OR REPLACE TRIGGER after_delete_trigger
AFTER DELETE ON my_table
FOR EACH ROW
BEGIN
-- Log the deletion into an audit table
INSERT INTO audit_table (action, table_name, row_id)
VALUES ('DELETE', 'my_table', :OLD.id);
END;
This trigger logs the deletion of a row from my_table into the audit_table.
47. What is a trigger and how is it different from constraints in Oracle?
A trigger is a stored procedure in Oracle that is automatically executed in response to certain events on a table or view, such as INSERT, UPDATE, or DELETE. Triggers are used to enforce business rules, perform validation, or automate tasks.
A constraint is a database object that is used to define rules for data in a table, such as PRIMARY KEY, FOREIGN KEY, CHECK, UNIQUE, and NOT NULL. Constraints are defined at the table level to enforce integrity rules.
Difference:
- Triggers are more flexible and can perform complex operations, while constraints are simple integrity checks.
- Triggers can modify data, raise exceptions, and log information, whereas constraints only enforce rules on the data.
48. Can triggers be fired for SELECT statements in Oracle?
No, triggers in Oracle cannot be fired for SELECT statements. Triggers are only fired in response to DML operations such as INSERT, UPDATE, and DELETE. You cannot create a trigger that is triggered by a SELECT statement.
49. What is a trigger event combination in Oracle?
A trigger event combination refers to the combination of the event (e.g., INSERT, UPDATE, DELETE) and the timing (e.g., BEFORE, AFTER, INSTEAD OF) that triggers the execution of a trigger. For example, a BEFORE INSERT trigger, AFTER UPDATE trigger, or INSTEAD OF DELETE trigger are all different event combinations that determine when and how a trigger is fired.
Example:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE UPDATE OR DELETE ON my_table
FOR EACH ROW
BEGIN
-- Trigger logic
END;
This trigger will fire before both update and delete operations on my_table.
50. How do you avoid infinite loops in Oracle triggers?
To avoid infinite loops in Oracle triggers, you should carefully manage dependencies between triggers. One approach is to use a flag or a session variable to track whether a trigger has already fired during the current operation. Another approach is to use conditional logic to ensure a trigger does not execute when it causes another trigger to fire.
Example using a session variable:
CREATE OR REPLACE TRIGGER my_trigger
BEFORE UPDATE ON my_table
FOR EACH ROW
BEGIN
IF :NEW.column_name IS NULL THEN
-- Prevent infinite loop
RETURN;
END IF;
END;
51. What is a trigger with multiple events in Oracle?
A trigger with multiple events is a trigger that responds to more than one DML event (like INSERT, UPDATE, or DELETE). You can specify multiple events in a trigger, and the trigger will fire for any of those events.
Example:
CREATE OR REPLACE TRIGGER multi_event_trigger
AFTER INSERT OR DELETE ON my_table
FOR EACH ROW
BEGIN
-- Trigger logic for both INSERT and DELETE
END;
This trigger will fire after both INSERT and DELETE events on my_table.
52. Can you update multiple rows in a row-level trigger in Oracle?
No, you cannot update multiple rows directly in a row-level trigger because a row-level trigger is executed once for each row affected by the DML operation. If you need to update multiple rows, you would need to write a separate statement in the trigger body to perform the update.
53. How can you monitor trigger execution in Oracle?
You can monitor trigger execution by using auditing, logging, and tracing mechanisms. For example, you can add DBMS_OUTPUT statements or insert logging information into an audit table within the trigger. Additionally, you can enable FGA (Fine-Grained Auditing) or use the Oracle Database tracing capabilities (DBMS_MONITOR) to track trigger execution.
54. Can triggers be used for performance tuning in Oracle?
While triggers are primarily used for data integrity and automation, they can indirectly help with performance tuning by automating tasks that would otherwise require additional application logic. However, triggers should be used carefully, as they can negatively impact performance if they execute complex logic frequently or on large datasets.
55. How can you test a trigger in Oracle?
To test a trigger, you can perform the DML operation (e.g., INSERT, UPDATE, or DELETE) that fires the trigger and observe the results. You can also use DBMS_OUTPUT.PUT_LINE to print messages or check the audit tables/logging to ensure the trigger logic is working as expected.
Example:
-- Insert a row into the table to test the trigger
INSERT INTO my_table (column_name) VALUES ('Test Value');
After the insert, check the result or the log to ensure the trigger fired correctly.
56. How can you get the list of triggers on a table in Oracle?
You can query the ALL_TRIGGERS or USER_TRIGGERS view to get a list of triggers associated with a table in Oracle.
Example:
SELECT trigger_name
FROM user_triggers
WHERE table_name = 'MY_TABLE';
57. What is the role of the "AFTER" clause in an Oracle trigger?
The AFTER clause in a trigger specifies that the trigger should fire after the associated DML event (e.g., INSERT, UPDATE, or DELETE) has occurred on the table. AFTER triggers are useful for actions that should occur once the data modification is complete, such as logging, auditing, or cascading updates.
58. What happens if a trigger fails during a DML operation in Oracle?
If a trigger fails during a DML operation, the operation (e.g., INSERT, UPDATE, or DELETE) is rolled back, and the trigger error is raised. The DML operation will not be applied, and the transaction will be aborted. You can catch the error in the trigger using exception handling.
59. Can a trigger raise an exception in Oracle?
Yes, a trigger can raise an exception using the RAISE_APPLICATION_ERROR procedure or any other exception. This is useful for enforcing custom business rules or validating data before committing changes.
Example:
RAISE_APPLICATION_ERROR(-20001, 'Custom error message');
60. What are system-level triggers in Oracle?
System-level triggers in Oracle are triggers that fire in response to system events, such as LOGON, LOGOFF, STARTUP, SHUTDOWN, and CREATE. These triggers are used for database-level operations like auditing user logins, performing initialization tasks, or controlling access.
Example:
CREATE OR REPLACE TRIGGER logon_trigger
AFTER LOGON ON DATABASE
BEGIN
-- Logic to perform after user logs on
END;
No comments:
Post a Comment