EMPLOYEE_DELETING

<DEFAULT SERVER>   >   <DEFAULT DATABASE>   >   <DEFAULT SCHEMA>   >   EMPLOYEE_DELETING

Simple Trigger of AFTER "DELETE ON EMPLOYEE"

Name	Types	Value
C1	Variable
Impact Analysis:	Object calling \"C1\" Object Type <DEFAULT SCHEMA>.EMPLOYEE_DELETING Trigger
MANAGERID	Variable
Impact Analysis:	Object calling \"MANAGERID\" Object Type <DEFAULT SCHEMA>.EMPLOYEE_DELETING Trigger <DEFAULT SCHEMA>.EMPLOYEE_DELETING.Update Update Statement
N	Variable
Impact Analysis:	Object calling \"N\" Object Type <DEFAULT SCHEMA>.EMPLOYEE_DELETING Trigger <DEFAULT SCHEMA>.EMPLOYEE_DELETING.Select Select Statement

CREATE OR REPLACE TRIGGER Employee_Deleting
AFTER DELETE ON EMPLOYEE
FOR EACH ROW
DECLARE
    
      cursor c1 is
        SELECT manager_fname
        FROM ALL_PRODUCTS where Manager_Id = :old.EMP_ID;
      
      managerId INTEGER;
      n INTEGER;
  
    
BEGIN
    
    managerId := Employees.GetManagerByEmployee(:old.EMP_ID);
    for c1 in c loop
      UPDATE PRODUCT
        SET MANAGER_ID = managerId
        WHERE c.ID = Id;
    
    end loop;


    SELECT COUNT(*) INTO n FROM EMPLOYEE;
    DBMS_OUTPUT.PUT_LINE(' There are now ' || n || ' employees.');
        
    Employees.LogDeletingEmployee(:new.EMP_ID);
    
END

Name	Types	Description
<DEFAULT SCHEMA>.EMPLOYEES	Package	Package, Interface
<DEFAULT SCHEMA>.EMPLOYEE	Table	Table
<DEFAULT SCHEMA>.PRODUCT	Table	Table
<DEFAULT SCHEMA>.ALL_PRODUCTS	View	View
<DEFAULT SCHEMA>.EMPLOYEES.LogDeletingEmployee	Procedure	Procedure interface LogDeletingEmployee(id NUMBER), Package EMPLOYEES
COUNT	Function	PL/SQL System Function
<DEFAULT SCHEMA>.EMPLOYEES.GetManagerByEmployee	Function	Function interface GetManagerByEmployee(id NUMBER) return INT, Package EMPLOYEES
DBMS_OUTPUT.PUT_LINE	Function	PL/SQL System Function
<DEFAULT SCHEMA>.ALL_PRODUCTS.MANAGER_ID	Column	View column
INTEGER	PL/SQL System type	PL/SQL System Object
PUBLIC.DBMS_OUTPUT	Synonym	PL/SQL System Synonym
C	Undefined Item
ID	Undefined Item
MANAGER_FNAME	Undefined Item
<DEFAULT SCHEMA>.EMPLOYEE.EMP_ID	Primary Key	Column
<DEFAULT SCHEMA>.PRODUCT.ID	Primary Key	Column
<DEFAULT SCHEMA>.PRODUCT.MANAGER_ID	Foreign Key	Column

Name	Types	Description
<DEFAULT SCHEMA>.EMPLOYEES.DeleteEmployeeById.Delete	Delete Statement	DELETE EMPLOYEE where emp_id = id