--2.5 Create a trigger that prevents the insertion of new employees whose salary is higher than 3000, and list the information of the inserted employees and rejected employees in the message window like this: --8654 ELSA INSERTED --8521 ALICE REJECTED --9499 LEE INSERTED --8698 FRED INSERTED --9655 ANDREW INSERTED --8902 LANCE REJECTED --Hint: In the trigger, use cursor to list the information of the new employees in the message window.
时间: 2024-02-28 16:57:28 浏览: 71
Sure, here is the trigger that meets your requirements:
```
CREATE OR REPLACE TRIGGER salary_check
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
salary_exceededException EXCEPTION;
PRAGMA EXCEPTION_INIT(salary_exceededException, -20001);
BEGIN
IF :NEW.salary > 3000 THEN
RAISE salary_exceededException;
END IF;
DBMS_OUTPUT.PUT_LINE(:NEW.employee_id || ' ' || :NEW.employee_name || ' INSERTED');
EXCEPTION
WHEN salary_exceededException THEN
DBMS_OUTPUT.PUT_LINE(:NEW.employee_id || ' ' || :NEW.employee_name || ' REJECTED');
END;
/
```
This trigger will be executed before any new record is inserted into the `employees` table. If the salary of the new employee exceeds 3000, the trigger will raise an exception and the insertion will be rejected. Otherwise, the trigger will list the information of the inserted employee in the message window using `DBMS_OUTPUT.PUT_LINE`. The rejected employees will also be listed in the message window.
Note that this trigger assumes that the `employee_id`, `employee_name`, and `salary` columns exist in the `employees` table. You may need to modify the column names to match your actual table schema.
阅读全文