Test Your Knowledge of Oracle DB Triggers with This Quiz

Progress

True / False

In ORACLE, a trigger is a PL/SQL block that automatically executes in response to specific events on a table or view.

True False

Triggers are special PL/SQL blocks that are automatically executed when certain events, such as INSERT, UPDATE, or DELETE, occur on a specified table or view.

True / False

In ORACLE, triggers can only be set to fire before an event occurs.

True False

Triggers in ORACLE can be set to fire before or after an event occurs.

True / False

In ORACLE, a trigger can only be associated with one table.

True False

Each trigger is associated with a single table or view, though multiple triggers can be defined for different events on the same table.

True / False

In ORACLE, triggers can be used to enforce complex business rules that cannot be enforced by constraints alone.

True False

Triggers can enforce complex business rules and validations that go beyond what standard database constraints can handle.

True / False

In ORACLE, it is possible to create a trigger that fires for each row affected by a DML statement.

True False

Row-level triggers fire once for each row affected by a DML statement, allowing detailed control over row-level operations.

True / False

In ORACLE, triggers cannot call stored procedures.

True False

Triggers can call stored procedures, enabling complex logic to be executed as part of the trigger action.

True / False

In ORACLE, you can create a trigger that fires for DDL events, such as CREATE or ALTER statements.

True False

ORACLE supports DDL triggers that can fire in response to DDL events like CREATE, ALTER, or DROP statements.

True / False

In ORACLE, a trigger can be defined to execute after a database startup event.

True False

System-level triggers can be defined to execute on database events such as startup, shutdown, or user logon.

True / False

In ORACLE, the WHEN clause in a trigger definition allows conditional execution of the trigger logic.

True False

The WHEN clause specifies a condition that must be met for the trigger to execute, providing more granular control over trigger execution.

Q10

True / False

In ORACLE, mutating table errors occur when a row-level trigger tries to read or modify a table that is currently being modified by the statement that fired the trigger.

True False

Mutating table errors occur when a row-level trigger attempts to query or modify the table that is currently being modified by the triggering statement, causing inconsistencies.

Q11

Multiple Choice

Identify the SQL statements that correctly create a simple BEFORE INSERT trigger in Oracle.

SQL Code

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
 :NEW.created_at := SYSDATE;
END;

CREATE OR REPLACE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN :NEW.created_at := SYSDATE; END; CREATE TRIGGER before_insert_employee ON employees BEFORE INSERT FOR EACH ROW BEGIN :NEW.created_at := SYSDATE; END; CREATE OR REPLACE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW BEGIN :NEW.created_at := SYSDATE; END;CREATE OR REPLACE TRIGGER before_insert_employee BEFORE INSERT ON employees FOR EACH ROW AS BEGIN :NEW.created_at := SYSDATE; END;

A BEFORE INSERT trigger is executed before an INSERT operation on a table. The correct SQL statements will follow the syntax for creating a trigger, specifying the timing, event, and logic.

Q12

Multiple Choice

Select the SQL statements that correctly create an AFTER UPDATE trigger in Oracle.

CREATE OR REPLACE TRIGGER after_update_salary AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; CREATE TRIGGER after_update_salary ON employees AFTER UPDATE FOR EACH ROW BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; CREATE OR REPLACE TRIGGER after_update_salary AFTER UPDATE ON employees FOR EACH ROW BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END;CREATE OR REPLACE TRIGGER after_update_salary AFTER UPDATE OF salary ON employees FOR EACH ROW AS BEGIN INSERT INTO salary_log (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END;

An AFTER UPDATE trigger is executed after an UPDATE operation on a table. The correct SQL statements will define the trigger with the appropriate timing and event, and include logic to log the changes.

Q13

Multiple Choice

Determine the SQL statements that demonstrate the creation of a BEFORE DELETE trigger in Oracle.

CREATE OR REPLACE TRIGGER before_delete_department BEFORE DELETE ON departments FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM employees WHERE department_id = :OLD.department_id) THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with employees'); END IF; END; CREATE TRIGGER before_delete_department ON departments BEFORE DELETE FOR EACH ROW BEGIN IF EXISTS (SELECT 1 FROM employees WHERE department_id = :OLD.department_id) THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with employees'); END IF; END; CREATE OR REPLACE TRIGGER before_delete_department BEFORE DELETE ON departments FOR EACH ROW BEGIN IF (SELECT COUNT(*) FROM employees WHERE department_id = :OLD.department_id) > 0 THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with employees'); END IF; END;CREATE OR REPLACE TRIGGER before_delete_department BEFORE DELETE ON departments FOR EACH ROW AS BEGIN IF EXISTS (SELECT 1 FROM employees WHERE department_id = :OLD.department_id) THEN RAISE_APPLICATION_ERROR(-20001, 'Cannot delete department with employees'); END IF; END;

A BEFORE DELETE trigger is executed before a DELETE operation on a table. The correct SQL statements will define the trigger with the appropriate timing and event, and include logic to prevent deletion based on a condition.

Q14

Multiple Choice

Identify the SQL statements that correctly create a compound trigger in Oracle.

CREATE OR REPLACE TRIGGER order_value_audit FOR UPDATE ON orders COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :NEW.total_value := :OLD.quantity * :OLD.price; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN INSERT INTO order_log (order_id, old_value, new_value, change_date) VALUES (:OLD.order_id, :OLD.total_value, :NEW.total_value, SYSDATE); END AFTER EACH ROW; END; CREATE TRIGGER order_value_audit ON orders FOR UPDATE COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :NEW.total_value := :OLD.quantity * :OLD.price; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN INSERT INTO order_log (order_id, old_value, new_value, change_date) VALUES (:OLD.order_id, :OLD.total_value, :NEW.total_value, SYSDATE); END AFTER EACH ROW; END; CREATE OR REPLACE TRIGGER order_value_audit AFTER UPDATE ON orders COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :NEW.total_value := :OLD.quantity * :OLD.price; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN INSERT INTO order_log (order_id, old_value, new_value, change_date) VALUES (:OLD.order_id, :OLD.total_value, :NEW.total_value, SYSDATE); END AFTER EACH ROW; END;CREATE OR REPLACE TRIGGER order_value_audit FOR UPDATE OF total_value ON orders COMPOUND TRIGGER BEFORE EACH ROW IS BEGIN :NEW.total_value := :OLD.quantity * :OLD.price; END BEFORE EACH ROW; AFTER EACH ROW IS BEGIN INSERT INTO order_log (order_id, old_value, new_value, change_date) VALUES (:OLD.order_id, :OLD.total_value, :NEW.total_value, SYSDATE); END AFTER EACH ROW; END;

Compound triggers allow you to define multiple timing points within a single trigger. The correct SQL statements will include sections for each timing point, such as BEFORE and AFTER events.

Q15

Multiple Choice

Choose the SQL statements that correctly create an INSTEAD OF trigger on a view in Oracle.

CREATE OR REPLACE TRIGGER instead_of_update_employee_dept INSTEAD OF UPDATE ON employee_dept_view FOR EACH ROW BEGIN UPDATE employees SET department_id = :NEW.department_id WHERE employee_id = :OLD.employee_id; END; CREATE TRIGGER instead_of_update_employee_dept ON employee_dept_view INSTEAD OF UPDATE FOR EACH ROW BEGIN UPDATE employees SET department_id = :NEW.department_id WHERE employee_id = :OLD.employee_id; END; CREATE OR REPLACE TRIGGER instead_of_update_employee_dept INSTEAD OF UPDATE ON employee_dept_view FOR EACH ROW AS BEGIN UPDATE employees SET department_id = :NEW.department_id WHERE employee_id = :OLD.employee_id; END;CREATE OR REPLACE TRIGGER instead_of_update_employee_dept INSTEAD OF UPDATE ON employee_dept_view FOR EACH ROW BEGIN UPDATE departments SET department_name = :NEW.department_name WHERE department_id = :OLD.department_id; END;

INSTEAD OF triggers are used to perform custom operations on views when DML statements are issued. The correct SQL statements will define the trigger with the appropriate event and logic to update the underlying tables.

Q16

Multiple Choice

Which SQL statements demonstrate the creation of a trigger with conditional logic based on OLD and NEW values in Oracle?

CREATE OR REPLACE TRIGGER update_last_updated AFTER UPDATE ON employees FOR EACH ROW BEGIN IF :OLD.salary != :NEW.salary THEN :NEW.last_updated := SYSDATE; END IF; END; CREATE OR REPLACE TRIGGER update_last_updated BEFORE UPDATE ON employees FOR EACH ROW BEGIN IF :OLD.salary != :NEW.salary THEN :NEW.last_updated := SYSDATE; END IF; END; CREATE TRIGGER update_last_updated ON employees AFTER UPDATE FOR EACH ROW BEGIN IF :OLD.salary != :NEW.salary THEN :NEW.last_updated := SYSDATE; END IF; END;CREATE OR REPLACE TRIGGER update_last_updated AFTER UPDATE ON employees FOR EACH ROW AS BEGIN IF :OLD.salary != :NEW.salary THEN :NEW.last_updated := SYSDATE; END IF; END;

Triggers can include conditional logic to check differences between OLD and NEW values. The correct SQL statements will use IF conditions to compare OLD and NEW values and perform actions accordingly.

Q17

Multiple Choice

Determine the SQL statements that correctly create a trigger to enforce a business rule in Oracle.

CREATE OR REPLACE TRIGGER enforce_salary_increase_rule BEFORE UPDATE OF salary ON employees FOR EACH ROW BEGIN IF :NEW.salary > :OLD.salary * 1.20 THEN RAISE_APPLICATION_ERROR(-20002, 'Salary increase exceeds 20%'); END IF; END; CREATE TRIGGER enforce_salary_increase_rule ON employees BEFORE UPDATE FOR EACH ROW BEGIN IF :NEW.salary > :OLD.salary * 1.20 THEN RAISE_APPLICATION_ERROR(-20002, 'Salary increase exceeds 20%'); END IF; END; CREATE OR REPLACE TRIGGER enforce_salary_increase_rule BEFORE UPDATE OF salary ON employees FOR EACH ROW AS BEGIN IF :NEW.salary > :OLD.salary * 1.20 THEN RAISE_APPLICATION_ERROR(-20002, 'Salary increase exceeds 20%'); END IF; END;CREATE OR REPLACE TRIGGER enforce_salary_increase_rule AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN IF :NEW.salary > :OLD.salary * 1.20 THEN RAISE_APPLICATION_ERROR(-20002, 'Salary increase exceeds 20%'); END IF; END;

Triggers can enforce business rules by validating conditions before DML operations. The correct SQL statements will include logic to compare OLD and NEW values and raise an error if the condition is not met.

Q18

Multiple Choice

Identify the SQL statements that demonstrate the creation of a trigger that handles mutating table errors in Oracle.

CREATE OR REPLACE TRIGGER update_department_total AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE OR REPLACE TRIGGER update_department_total BEFORE INSERT ON employees FOR EACH ROW BEGIN UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE OR REPLACE TRIGGER update_department_total AFTER INSERT ON employees COMPOUND TRIGGER AFTER EACH ROW IS BEGIN UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END AFTER EACH ROW; END;CREATE OR REPLACE TRIGGER update_department_total AFTER INSERT ON employees FOR EACH ROW AS BEGIN UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END;

Mutating table errors occur when a trigger attempts to query or modify the table that is being modified by the triggering statement. The correct SQL statements will use row-level triggers and possibly compound triggers to avoid this error.

Q19

Multiple Choice

Choose the SQL statements that correctly create a trigger with a PRAGMA AUTONOMOUS_TRANSACTION directive in Oracle.

CREATE OR REPLACE TRIGGER log_employee_changes AFTER UPDATE ON employees FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO change_log (employee_id, change_date) VALUES (:OLD.employee_id, SYSDATE); COMMIT; END; CREATE TRIGGER log_employee_changes ON employees AFTER UPDATE FOR EACH ROW DECLARE PRAGMA AUTONOMOUS_TRANSACTION BEGIN INSERT INTO change_log (employee_id, change_date) VALUES (:OLD.employee_id, SYSDATE); COMMIT; END; CREATE OR REPLACE TRIGGER log_employee_changes AFTER UPDATE ON employees FOR EACH ROW PRAGMA AUTONOMOUS_TRANSACTION BEGIN INSERT INTO change_log (employee_id, change_date) VALUES (:OLD.employee_id, SYSDATE); COMMIT; END;CREATE OR REPLACE TRIGGER log_employee_changes AFTER UPDATE ON employees FOR EACH ROW AS DECLARE PRAGMA AUTONOMOUS_TRANSACTION; BEGIN INSERT INTO change_log (employee_id, change_date) VALUES (:OLD.employee_id, SYSDATE); COMMIT; END;

PRAGMA AUTONOMOUS_TRANSACTION allows a trigger to perform operations that commit independently of the main transaction. The correct SQL statements will include this directive within the trigger.

Q20

Multiple Choice

Select the SQL statements that demonstrate the creation of a trigger that updates related tables in Oracle.

CREATE OR REPLACE TRIGGER update_department_total_on_change AFTER UPDATE OF department_id ON employees FOR EACH ROW BEGIN UPDATE department_total SET total_employees = total_employees - 1 WHERE department_id = :OLD.department_id; UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE TRIGGER update_department_total_on_change ON employees AFTER UPDATE OF department_id FOR EACH ROW BEGIN UPDATE department_total SET total_employees = total_employees - 1 WHERE department_id = :OLD.department_id; UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE OR REPLACE TRIGGER update_department_total_on_change AFTER UPDATE OF department_id ON employees FOR EACH ROW AS BEGIN UPDATE department_total SET total_employees = total_employees - 1 WHERE department_id = :OLD.department_id; UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END;CREATE OR REPLACE TRIGGER update_department_total_on_change AFTER UPDATE ON employees FOR EACH ROW BEGIN UPDATE department_total SET total_employees = total_employees - 1 WHERE department_id = :OLD.department_id; UPDATE department_total SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END;

Triggers can be used to maintain referential integrity by updating related tables when changes are made to a parent table. The correct SQL statements will include logic to update the related table based on the changes.

Q21

Single Choice

How would you create a trigger to automatically update a timestamp column whenever a row is updated in a table?

SQL Code

CREATE OR REPLACE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
 :NEW.last_updated := SYSDATE;
END;

CREATE OR REPLACE TRIGGER update_timestamp BEFORE INSERT ON employees FOR EACH ROW BEGIN :NEW.last_updated := SYSDATE; END; CREATE OR REPLACE TRIGGER update_timestamp AFTER UPDATE ON employees FOR EACH ROW BEGIN :NEW.last_updated := SYSDATE; END; CREATE OR REPLACE TRIGGER update_timestamp BEFORE UPDATE ON employees FOR EACH ROW BEGIN :NEW.last_updated := SYSDATE; END;CREATE OR REPLACE TRIGGER update_timestamp BEFORE DELETE ON employees FOR EACH ROW BEGIN :NEW.last_updated := SYSDATE; END;

A trigger is created using the CREATE OR REPLACE TRIGGER statement, and it can be set to fire before an update operation to automatically update a column with the current timestamp.

Q22

Single Choice

Which type of trigger would you use to enforce a business rule that prevents salary increases of more than 20%?

SQL Code

CREATE OR REPLACE TRIGGER check_salary_increase
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary * 1.2)
BEGIN
 RAISE_APPLICATION_ERROR(-20001, 'Salary increase exceeds 20%');
END;

AFTER UPDATE trigger BEFORE INSERT trigger BEFORE UPDATE triggerAFTER DELETE trigger

A BEFORE UPDATE trigger with a WHEN clause is used to enforce business rules by validating changes before they are applied to the database.

Q23

Single Choice

How can you create a trigger to log changes to a specific column in a separate audit table?

SQL Code

CREATE OR REPLACE TRIGGER log_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
 INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date)
 VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;

CREATE OR REPLACE TRIGGER log_salary_changes BEFORE UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; CREATE OR REPLACE TRIGGER log_salary_changes AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; CREATE OR REPLACE TRIGGER log_salary_changes BEFORE DELETE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END;CREATE OR REPLACE TRIGGER log_salary_changes AFTER DELETE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END;

An AFTER UPDATE trigger is used to log changes by inserting the old and new values, along with a timestamp, into an audit table.

Q24

Single Choice

What type of trigger would you create to automatically populate a full name column when inserting data into a table?

SQL Code

CREATE OR REPLACE TRIGGER populate_full_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
 :NEW.full_name := :NEW.first_name || ' ' || :NEW.last_name;
END;

BEFORE INSERT trigger AFTER INSERT trigger BEFORE UPDATE triggerAFTER DELETE trigger

A BEFORE INSERT trigger is used to automatically populate a derived column, such as full_name, based on other column values before the data is inserted.

Q25

Single Choice

Which trigger would you use to prevent the deletion of rows in a specific table under certain conditions?

SQL Code

CREATE OR REPLACE TRIGGER prevent_deletion
BEFORE DELETE ON employees
FOR EACH ROW
WHEN (:OLD.job_title = 'Manager')
BEGIN
 RAISE_APPLICATION_ERROR(-20002, 'Cannot delete a manager');
END;

BEFORE INSERT trigger AFTER UPDATE trigger BEFORE DELETE triggerAFTER DELETE trigger

A BEFORE DELETE trigger with a WHEN clause is used to prevent the deletion of rows that meet specific criteria, such as employees with a particular job title.

Q26

Single Choice

How would you create a trigger that fires after an insert operation and updates another table based on the inserted data?

SQL Code

CREATE OR REPLACE TRIGGER update_department_total
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
 UPDATE departments SET total_employees = total_employees + 1
 WHERE department_id = :NEW.department_id;
END;

CREATE OR REPLACE TRIGGER update_department_total BEFORE INSERT ON employees FOR EACH ROW BEGIN UPDATE departments SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE OR REPLACE TRIGGER update_department_total AFTER INSERT ON employees FOR EACH ROW BEGIN UPDATE departments SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END; CREATE OR REPLACE TRIGGER update_department_total BEFORE DELETE ON employees FOR EACH ROW BEGIN UPDATE departments SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END;CREATE OR REPLACE TRIGGER update_department_total AFTER DELETE ON employees FOR EACH ROW BEGIN UPDATE departments SET total_employees = total_employees + 1 WHERE department_id = :NEW.department_id; END;

An AFTER INSERT trigger is used to perform additional actions, such as updating another table based on the data that was inserted.

Q27

Single Choice

Which trigger would you use to enforce that a column value must be uppercase before inserting into the database?

SQL Code

CREATE OR REPLACE TRIGGER enforce_uppercase
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
 :NEW.last_name := UPPER(:NEW.last_name);
END;

BEFORE INSERT trigger AFTER INSERT trigger BEFORE UPDATE triggerAFTER DELETE trigger

A BEFORE INSERT trigger can be used to enforce formatting rules, such as converting text to uppercase before the data is inserted into the database.

Q28

Single Choice

How can you create a trigger to automatically generate a unique value for a primary key column?

SQL Code

CREATE OR REPLACE TRIGGER generate_employee_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
 SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual;
END;

CREATE OR REPLACE TRIGGER generate_employee_id BEFORE INSERT ON employees FOR EACH ROW BEGIN SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual; END; CREATE OR REPLACE TRIGGER generate_employee_id AFTER INSERT ON employees FOR EACH ROW BEGIN SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual; END; CREATE OR REPLACE TRIGGER generate_employee_id BEFORE DELETE ON employees FOR EACH ROW BEGIN SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual; END;CREATE OR REPLACE TRIGGER generate_employee_id AFTER DELETE ON employees FOR EACH ROW BEGIN SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual; END;

A BEFORE INSERT trigger can be used to automatically generate a unique value for a primary key column using a sequence.

Q29

Single Choice

How would you create a trigger that fires before an update to enforce that a salary increase cannot exceed a specified amount?

SQL Code

CREATE OR REPLACE TRIGGER enforce_salary_increase_limit
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
WHEN (NEW.salary > OLD.salary + 1000)
BEGIN
 RAISE_APPLICATION_ERROR(-20003, 'Salary increase exceeds the limit');
END;

BEFORE UPDATE trigger AFTER UPDATE trigger BEFORE INSERT triggerAFTER DELETE trigger

A BEFORE UPDATE trigger with a WHEN clause can enforce limits on salary increases by raising an application error if the new salary exceeds the allowed increase.

Q30

Single Choice

Which trigger type would you use to execute a specific action after a database table is updated?

SQL Code

CREATE OR REPLACE TRIGGER audit_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
 INSERT INTO audit_log (change_date, user_name, operation)
 VALUES (SYSDATE, USER, 'UPDATE');
END;

BEFORE UPDATE trigger AFTER UPDATE trigger BEFORE INSERT triggerAFTER DELETE trigger

An AFTER UPDATE trigger is used to perform actions, such as logging or auditing, after an update operation has been completed on a table.

Oracle Database Quiz Questions