1. Basic BEFORE INSERT Trigger
CREATE OR REPLACE TRIGGER trg_before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.hire_date := SYSDATE;
END;
/
2. Basic AFTER INSERT Trigger
CREATE OR REPLACE TRIGGER trg_after_insert_employee
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (log_message)
VALUES ('Employee ' || :NEW.employee_id || ' inserted.');
END;
/
3. BEFORE UPDATE Trigger for Salary Increase
CREATE OR REPLACE TRIGGER trg_before_update_salary
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be decreased.');
END IF;
END;
/
4. AFTER UPDATE Trigger for Salary Log
CREATE OR REPLACE TRIGGER trg_after_update_salary
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;
/
5. BEFORE DELETE Trigge
CREATE OR REPLACE TRIGGER trg_before_delete_employee
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'MANAGER' THEN
RAISE_APPLICATION_ERROR(-20002, 'Managers cannot be deleted.');
END IF;
END;
/
6. AFTER DELETE Trigger
CREATE OR REPLACE TRIGGER trg_after_delete_employee
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_archive (employee_id, first_name, last_name, delete_date)
VALUES (:OLD.employee_id, :OLD.first_name, :OLD.last_name, SYSDATE);
END;
/
7. Trigger to Prevent Insert if Employee Already Exists
CREATE OR REPLACE TRIGGER trg_prevent_duplicate_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF EXISTS (SELECT 1 FROM employees WHERE email = :NEW.email) THEN
RAISE_APPLICATION_ERROR(-20003, 'Employee with this email already exists.');
END IF;
END;
/
8. Trigger to Set Default Values on Insert
CREATE OR REPLACE TRIGGER trg_default_values
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary IS NULL THEN
:NEW.salary := 3000;
END IF;
IF :NEW.department_id IS NULL THEN
:NEW.department_id := 10;
END IF;
END;
/
9. BEFORE UPDATE Trigger to Prevent Update of Primary Key
CREATE OR REPLACE TRIGGER trg_prevent_primary_key_update
BEFORE UPDATE OF employee_id ON employees
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20004, 'Primary key cannot be updated.');
END;
/
10. Trigger for Auditing Table Changes
CREATE OR REPLACE TRIGGER trg_audit_employee_changes
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_audit (operation, employee_id, change_date)
VALUES (CASE WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE' END,
:NEW.employee_id,
SYSDATE);
END;
/
11. Trigger to Auto-Increment ID on Insert
CREATE OR REPLACE TRIGGER trg_auto_increment_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
SELECT employee_seq.NEXTVAL INTO :NEW.employee_id FROM dual;
END;
/
12. Trigger to Log Changes in Job Title
CREATE OR REPLACE TRIGGER trg_log_job_title_changes
AFTER UPDATE OF job_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id <> :OLD.job_id THEN
INSERT INTO job_title_log (employee_id, old_job_id, new_job_id, change_date)
VALUES (:OLD.employee_id, :OLD.job_id, :NEW.job_id, SYSDATE);
END IF;
END;
/
13. Trigger to Deny Weekend Inserts
CREATE OR REPLACE TRIGGER trg_deny_weekend_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'DY') IN ('SAT', 'SUN') THEN
RAISE_APPLICATION_ERROR(-20005, 'Inserts are not allowed on weekends.');
END IF;
END;
/
14. Trigger to Update Managers Report Count
CREATE OR REPLACE TRIGGER trg_update_manager_report_count
AFTER INSERT OR DELETE ON employees
FOR EACH ROW
BEGIN
IF INSERTING THEN
UPDATE employees
SET report_count = report_count + 1
WHERE employee_id = :NEW.manager_id;
ELSIF DELETING THEN
UPDATE employees
SET report_count = report_count - 1
WHERE employee_id = :OLD.manager_id;
END IF;
END;
/
15. Trigger to Calculate Age on Insert
CREATE OR REPLACE TRIGGER trg_calculate_age
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.age := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.date_of_birth) / 12);
END;
/
16. Trigger to Prevent Future Hire Dates
CREATE OR REPLACE TRIGGER trg_prevent_future_hire_date
BEFORE INSERT OR UPDATE OF hire_date ON employees
FOR EACH ROW
BEGIN
IF :NEW.hire_date > SYSDATE THEN
RAISE_APPLICATION_ERROR(-20006, 'Hire date cannot be in the future.');
END IF;
END;
/
17. Trigger to Default Commission to Zero
CREATE OR REPLACE TRIGGER trg_default_commission
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.commission_pct IS NULL THEN
:NEW.commission_pct := 0;
END IF;
END;
/
18. Trigger to Log Updates to Employee Contact Information
CREATE OR REPLACE TRIGGER trg_log_contact_update
AFTER UPDATE OF email, phone_number ON employees
FOR EACH ROW
BEGIN
INSERT INTO contact_audit (employee_id, old_email, new_email, old_phone, new_phone, change_date)
VALUES (:OLD.employee_id, :OLD.email, :NEW.email, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END;
/
19. Trigger to Prevent Deletion of Critical Data
CREATE OR REPLACE TRIGGER trg_prevent_critical_data_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.department_id = 10 THEN
RAISE_APPLICATION_ERROR(-20007, 'Employees from department 10 cannot be deleted.');
END IF;
END;
/
20. Trigger to Auto-Fill Full Name on Insert
CREATE OR REPLACE TRIGGER trg_auto_fill_full_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.full_name := :NEW.first_name || ' ' || :NEW.last_name;
END;
/
21. Trigger to Ensure Minimum Salary
CREATE OR REPLACE TRIGGER trg_ensure_minimum_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 2000 THEN
:NEW.salary := 2000;
END IF;
END;
/
22. Trigger to Log Promotions
CREATE OR REPLACE TRIGGER trg_log_promotions
AFTER UPDATE OF job_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id <> :OLD.job_id THEN
INSERT INTO promotion_log (employee_id, old_job_id, new_job_id, promotion_date)
VALUES (:OLD.employee_id, :OLD.job_id, :NEW.job_id, SYSDATE);
END IF;
END;
/
23. Trigger to Calculate Service Duration on Update
CREATE OR REPLACE TRIGGER trg_calculate_service_duration
AFTER UPDATE OF hire_date ON employees
FOR EACH ROW
BEGIN
:NEW.service_duration := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.hire_date) / 12);
END;
/
24. Trigger to Audit Salary Changes
CREATE OR REPLACE TRIGGER trg_audit_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit_log (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
25. Trigger to Restrict Updates During Business Hours
CREATE OR REPLACE TRIGGER trg_restrict_updates
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'HH24') BETWEEN 9 AND 17 THEN
R
AISE_APPLICATION_ERROR(-20008, 'Updates are not allowed during business hours.');
END IF;
END;
/
26. Trigger to Log Employee Termination
CREATE OR REPLACE TRIGGER trg_log_termination
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO termination_log (employee_id, termination_date)
VALUES (:OLD.employee_id, SYSDATE);
END;
/
27. Trigger to Prevent Deletion of Active Employees
CREATE OR REPLACE TRIGGER trg_prevent_active_employee_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.status = 'ACTIVE' THEN
RAISE_APPLICATION_ERROR(-20009, 'Active employees cannot be deleted.');
END IF;
END;
/
28. Trigger to Enforce Unique Email
CREATE OR REPLACE TRIGGER trg_enforce_unique_email
BEFORE INSERT OR UPDATE OF email ON employees
FOR EACH ROW
DECLARE
email_count INTEGER;
BEGIN
SELECT COUNT(*) INTO email_count
FROM employees
WHERE email = :NEW.email AND employee_id != :NEW.employee_id;
IF email_count > 0 THEN
RAISE_APPLICATION_ERROR(-20010, 'Email must be unique.');
END IF;
END;
/
29. Trigger to Prevent Insert During Maintenance Window
CREATE OR REPLACE TRIGGER trg_prevent_maintenance_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'DY') = 'SUN' THEN
RAISE_APPLICATION_ERROR(-20011, 'Inserts are not allowed during maintenance.');
END IF;
END;
/
30. Trigger to Log Manager Changes
CREATE OR REPLACE TRIGGER trg_log_manager_changes
AFTER UPDATE OF manager_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.manager_id <> :OLD.manager_id THEN
INSERT INTO manager_change_log (employee_id, old_manager_id, new_manager_id, change_date)
VALUES (:OLD.employee_id, :OLD.manager_id, :NEW.manager_id, SYSDATE);
END IF;
END;
/
31. Trigger to Enforce Maximum Number of Reports
CREATE OR REPLACE TRIGGER trg_enforce_max_reports
BEFORE INSERT OR UPDATE OF manager_id ON employees
FOR EACH ROW
DECLARE
report_count INTEGER;
BEGIN
SELECT COUNT(*) INTO report_count
FROM employees
WHERE manager_id = :NEW.manager_id;
IF report_count >= 10 THEN
RAISE_APPLICATION_ERROR(-20012, 'Manager already has maximum number of reports.');
END IF;
END;
/
32. Trigger to Automatically Set Employee Status
CREATE OR REPLACE TRIGGER trg_set_employee_status
BEFORE INSERT OR UPDATE OF end_date ON employees
FOR EACH ROW
BEGIN
IF :NEW.end_date IS NOT NULL THEN
:NEW.status := 'INACTIVE';
ELSE
:NEW.status := 'ACTIVE';
END IF;
END;
/
33. Trigger to Deny Update of Critical Fields
CREATE OR REPLACE TRIGGER trg_deny_critical_update
BEFORE UPDATE OF first_name, last_name, department_id ON employees
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20013, 'These fields cannot be updated.');
END;
/
34. Trigger to Prevent Negative Salary
CREATE OR REPLACE TRIGGER trg_prevent_negative_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20014, 'Salary cannot be negative.');
END IF;
END;
/
35. Trigger to Set Default Hire Date
CREATE OR REPLACE TRIGGER trg_default_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;
END;
/
36. Trigger to Log Email Address Changes
CREATE OR REPLACE TRIGGER trg_log_email_changes
AFTER UPDATE OF email ON employees
FOR EACH ROW
BEGIN
IF :NEW.email <> :OLD.email THEN
INSERT INTO email_change_log (employee_id, old_email, new_email, change_date)
VALUES (:OLD.employee_id, :OLD.email, :NEW.email, SYSDATE);
END IF;
END;
/
37. Trigger to Restrict Deletion of Employees with Reports
CREATE OR REPLACE TRIGGER trg_restrict_employee_with_reports
BEFORE DELETE ON employees
FOR EACH ROW
DECLARE
report_count INTEGER;
BEGIN
SELECT COUNT(*) INTO report_count
FROM employees
WHERE manager_id = :OLD.employee_id;
IF report_count > 0 THEN
RAISE_APPLICATION_ERROR(-20015, 'Employees with reports cannot be deleted.');
END IF;
END;
/
38. Trigger to Auto-Assign Manager
CREATE OR REPLACE TRIGGER trg_auto_assign_manager
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.manager_id IS NULL THEN
:NEW.manager_id := (SELECT manager_id FROM department_managers WHERE department_id = :NEW.department_id);
END IF;
END;
/
39. Trigger to Log Department Changes
CREATE OR REPLACE TRIGGER trg_log_department_changes
AFTER UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id <> :OLD.department_id THEN
INSERT INTO department_change_log (employee_id, old_department_id, new_department_id, change_date)
VALUES (:OLD.employee_id, :OLD.department_id, :NEW.department_id, SYSDATE);
END IF;
END;
/
40. Trigger to Deny Deletion of Recent Hires
CREATE OR REPLACE TRIGGER trg_deny_recent_hire_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF MONTHS_BETWEEN(SYSDATE, :OLD.hire_date) < 6 THEN
RAISE_APPLICATION_ERROR(-20016, 'Recent hires cannot be deleted.');
END IF;
END;
/
41. Trigger to Restrict Salary Increase to Specific Percentage
CREATE OR REPLACE TRIGGER trg_restrict_salary_increase
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > :OLD.salary * 1.10 THEN
RAISE_APPLICATION_ERROR(-20017, 'Salary increase cannot exceed 10%.');
END IF;
END;
/
42. Trigger to Prevent Updates to Terminated Employees
CREATE OR REPLACE TRIGGER trg_prevent_terminated_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.status = 'TERMINATED' THEN
RAISE_APPLICATION_ERROR(-20018, 'Terminated employees cannot be updated.');
END IF;
END;
/
43. Trigger to Enforce Salary Range
CREATE OR REPLACE TRIGGER trg_enforce_salary_range
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 2000 OR :NEW.salary > 10000 THEN
RAISE_APPLICATION_ERROR(-20019, 'Salary must be between 2000 and 10000.');
END IF;
END;
/
44. Trigger to Log Bonus Changes
CREATE OR REPLACE TRIGGER trg_log_bonus_changes
AFTER UPDATE OF bonus ON employees
FOR EACH ROW
BEGIN
INSERT INTO bonus_audit (employee_id, old_bonus, new_bonus, change_date)
VALUES (:OLD.employee_id, :OLD.bonus, :NEW.bonus, SYSDATE);
END;
/
45. Trigger to Automatically Set Full Name
CREATE OR REPLACE TRIGGER trg_auto_set_full_name
BEFORE INSERT OR UPDATE OF first_name, last_name ON employees
FOR EACH ROW
BEGIN
:NEW.full_name := :NEW.first_name || ' ' || :NEW.last_name;
END;
/
46. Trigger to Restrict Updates Based on Role
CREATE OR REPLACE TRIGGER trg_restrict_update_based_on_role
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF USER NOT IN ('HR_ADMIN', 'HR_MANAGER') THEN
RAISE_APPLICATION_ERROR(-20020, 'You are not authorized to update employee records.');
END IF;
END;
/
47. Trigger to Prevent Multiple Active Assignments
CREATE OR REPLACE TRIGGER trg_prevent_multiple_active_assignments
BEFORE INSERT OR UPDATE OF status ON employees
FOR EACH ROW
DECLARE
active_count INTEGER;
BEGIN
SELECT COUNT(*) INTO active_count
FROM employees
WHERE employee_id = :NEW.employee_id AND status = 'ACTIVE';
IF active_count > 0 AND :NEW.status = 'ACTIVE' THEN
RAISE_APPLICATION_ERROR(-20021, 'Employee cannot have multiple active assignments.');
END IF;
END;
/
48. Trigger to Log Termination Reason
CREATE OR REPLACE TRIGGER trg_log_termination_reason
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO termination_reason_log (employee_id, termination_reason, termination_date)
VALUES (:OLD.employee_id, :OLD.termination_reason, SYSDATE);
END;
/
49. Trigger to Prevent Update of Salary for Executives
CREATE OR REPLACE TRIGGER trg_prevent_executive_salary_update
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'EXECUTIVE' THEN
RAISE_APPLICATION_ERROR(-20022, 'Salary for executives cannot be updated.');
END IF;
END;
/
50. Trigger to Enforce Employee Age Limit
CREATE OR REPLACE TRIGGER trg_enforce_age_limit
BEFORE INSERT OR UPDATE OF date_of_birth ON employees
FOR EACH ROW
DECLARE
age INTEGER;
BEGIN
age := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.date_of_birth) / 12);
IF age < 18 THEN
RAISE_APPLICATION_ERROR(-20023, 'Employee must be at least 18 years old.');
END IF;
END;
/
51. Trigger to Log Updates to Employee Address
CREATE OR REPLACE TRIGGER trg_log_address_update
AFTER UPDATE OF address ON employees
FOR EACH ROW
BEGIN
INSERT INTO address_audit (employee_id, old_address, new_address, change_date)
VALUES (:OLD.employee_id, :OLD.address, :NEW.address, SYSDATE);
END;
/
52. Trigger to Auto-Fill Department on Insert
CREATE OR REPLACE TRIGGER trg_auto_fill_department
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id IS NULL THEN
:NEW.department_id := (SELECT department_id FROM departments WHERE department_name = 'Sales');
END IF;
END;
/
53. Trigger to Deny Updates During Non-Business Hours
CREATE OR REPLACE TRIGGER trg_deny_updates_non_business_hours
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF TO_CHAR(SYSDATE, 'HH24') NOT BETWEEN 9 AND 17 THEN
RAISE_APPLICATION_ERROR(-20024, 'Updates are not allowed outside business hours.');
END IF;
END;
/
54. Trigger to Log Changes to Contact Information
CREATE OR REPLACE TRIGGER trg_log_contact_information_changes
AFTER UPDATE OF email, phone_number, address ON employees
FOR EACH ROW
BEGIN
INSERT INTO contact_information_audit (employee_id, old_email, new_email, old_phone, new_phone, old_address, new_address, change_date)
VALUES (:OLD.employee_id, :OLD.email, :NEW.email, :OLD.phone_number, :NEW.phone_number, :OLD.address, :NEW.address, SYSDATE);
END;
/
55. Trigger to Prevent Update of Department for Managers
CREATE OR REPLACE TRIGGER trg_prevent_manager_department_update
BEFORE UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'MANAGER' THEN
RAISE_APPLICATION_ERROR(-20025, 'Department for managers cannot be updated.');
END IF;
END;
/
56. Trigger to Log Updates to Salary
CREATE OR REPLACE TRIGGER trg_log_salary_updates
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_update_log (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
57. Trigger to Enforce Manager Assignment
CREATE OR REPLACE TRIGGER trg_enforce_manager_assignment
BEFORE INSERT OR UPDATE OF manager_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id = 'EMPLOYEE' AND :NEW.manager_id IS NULL THEN
RAISE_APPLICATION_ERROR(-20026, 'Employee must have a manager assigned.');
END IF;
END;
/
58. Trigger to Deny Insert of Duplicate Email
CREATE OR REPLACE TRIGGER trg_deny_duplicate_email_insert
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
email_count INTEGER;
BEGIN
SELECT COUNT(*) INTO email_count
FROM employees
WHERE email = :NEW.email;
IF email_count > 0 THEN
RAISE_APPLICATION_ERROR(-20027, 'Email already exists.');
END IF;
END;
/
59. Trigger to Log Changes to Job ID
CREATE OR REPLACE TRIGGER trg_log_job_id_changes
AFTER UPDATE OF job_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id <> :OLD.job_id THEN
INSERT INTO job_id_audit (employee_id, old_job_id, new_job_id, change_date)
VALUES (:OLD.employee_id, :OLD.job_id, :NEW.job_id, SYSDATE);
END IF;
END;
/
60. Trigger to Prevent Update of Manager ID
CREATE OR REPLACE TRIGGER trg_prevent_manager_id_update
BEFORE UPDATE OF manager_id ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'MANAGER' THEN
RAISE_APPLICATION_ERROR(-20028, 'Manager ID cannot be updated.');
END IF;
END;
/
61. Trigger to Auto-Assign Default Department
CREATE OR REPLACE TRIGGER trg_auto_assign_default_department
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id IS NULL THEN
:NEW.department_id := 1;
END IF;
END;
/
62. Trigger to Restrict Updates to Certain Columns
CREATE OR REPLACE TRIGGER trg_restrict_column_updates
BEFORE UPDATE OF first_name, last_name ON employees
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20029, 'First name and last name cannot be updated.');
END;
/
63. Trigger to Enforce Unique Combination of Columns
CREATE OR REPLACE TRIGGER trg_enforce_unique_combination
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
combination_count INTEGER;
BEGIN
SELECT COUNT(*) INTO combination_count
FROM employees
WHERE first_name = :NEW.first_name AND last_name = :NEW.last_name AND date_of_birth = :NEW.date_of_birth;
IF combination_count > 0 THEN
RAISE_APPLICATION_ERROR(-20030, 'Combination of first name, last name, and date of birth must be unique.');
END IF;
END;
/
64. Trigger to Log Deletions
CREATE OR REPLACE TRIGGER trg_log_deletions
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO deletion_log (employee_id, deletion_date)
VALUES (:OLD.employee_id, SYSDATE);
END;
/
65. Trigger to Enforce Non-Negative Bonus
CREATE OR REPLACE TRIGGER trg_enforce_non_negative_bonus
BEFORE INSERT OR UPDATE OF bonus ON employees
FOR EACH ROW
BEGIN
IF :NEW.bonus < 0 THEN
RAISE_APPLICATION_ERROR(-20031, 'Bonus cannot be negative.');
END IF;
END;
/
66. Trigger to Automatically Set Status Based on Date
CREATE OR REPLACE TRIGGER trg_auto_set_status_based_on_date
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.end_date IS NOT NULL THEN
:NEW.status := 'INACTIVE';
ELSE
:NEW.status := 'ACTIVE';
END IF;
END;
/
67. Trigger to Prevent Insert if Department is Full
CREATE OR REPLACE TRIGGER trg_prevent_insert_if_department_full
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
department_count INTEGER;
BEGIN
SELECT COUNT(*) INTO department_count
FROM employees
WHERE department_id = :NEW.department_id;
IF department_count >= 100 THEN
RAISE_APPLICATION_ERROR(-20032, 'Department is full.');
END IF;
END;
/
68. Trigger to Log Changes to Phone Number
CREATE OR REPLACE TRIGGER trg_log_phone_number_changes
AFTER UPDATE OF phone_number ON employees
FOR EACH ROW
BEGIN
IF :NEW.phone_number <> :OLD.phone_number THEN
INSERT INTO phone_number_audit (employee_id, old_phone_number, new_phone_number, change_date)
VALUES (:OLD.employee_id, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END IF;
END;
/
69. Trigger to Restrict Updates to Active Employees
CREATE OR REPLACE TRIGGER trg_restrict_updates_to_active_employees
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.status <> 'ACTIVE' THEN
RAISE_APPLICATION_ERROR(-20033, 'Updates are only allowed for active employees.');
END IF;
END;
/
70. Trigger to Enforce Salary Based on Job ID
CREATE OR REPLACE TRIGGER trg_enforce_salary_based_on_job_id
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id = 'MANAGER' AND :NEW.salary < 5000 THEN
RAISE_APPLICATION_ERROR(-20034, 'Salary for managers must be at least 5000.');
END IF;
END;
/
71. Trigger to Log Insertions
CREATE OR REPLACE TRIGGER trg_log_insertions
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO insertion_log (employee_id, insertion_date)
VALUES (:NEW.employee_id, SYSDATE);
END;
/
72. Trigger to Prevent Updates Based on Date
CREATE OR REPLACE TRIGGER trg_prevent_updates_based_on_date
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.hire_date < TO_DATE('01-JAN-2020', 'DD-MON-YYYY') THEN
RAISE_APPLICATION_ERROR(-20035, 'Records created before 2020 cannot be updated.');
END IF;
END;
/
73. Trigger to Automatically Assign Job ID
CREATE OR REPLACE TRIGGER trg_auto_assign_job_id
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id IS NULL THEN
:NEW.job_id := 'EMPLOYEE';
END IF;
END;
/
74. Trigger to Enforce Unique Phone Number
CREATE OR REPLACE TRIGGER trg_enforce_unique_phone_number
BEFORE INSERT OR UPDATE OF phone_number ON employees
FOR EACH ROW
DECLARE
phone_count INTEGER;
BEGIN
SELECT COUNT(*) INTO phone_count
FROM employees
WHERE phone_number = :NEW.phone_number AND employee_id != :NEW.employee_id;
IF phone_count > 0 THEN
RAISE_APPLICATION_ERROR(-20036, 'Phone number must be unique.');
END IF;
END;
/
75. Trigger to Log Updates to Department ID
CREATE OR REPLACE TRIGGER trg_log_department_id_updates
AFTER UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id <> :OLD.department_id THEN
INSERT INTO department_id_audit (employee_id, old_department_id, new_department_id, change_date)
VALUES (:OLD.employee_id, :OLD.department_id, :NEW.department_id, SYSDATE);
END IF;
END;
/
76. Trigger to Restrict Updates to Salary
CREATE OR REPLACE TRIGGER trg_restrict_salary_updates
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 10000 THEN
RAISE_APPLICATION_ERROR(-20037, 'Salary cannot exceed 10000.');
END IF;
END;
/
77. Trigger to Automatically Set Job ID Based on Department
CREATE OR REPLACE TRIGGER trg_auto_set_job_id_based_on_department
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id = 10 THEN
:NEW.job_id := 'MANAGER';
ELSE
:NEW.job_id := 'EMPLOYEE';
END IF;
END;
/
78. Trigger to Log Changes to Email Address
CREATE OR REPLACE TRIGGER trg_log_email_address_changes
AFTER UPDATE OF email ON employees
FOR EACH ROW
BEGIN
IF :NEW.email <> :OLD.email THEN
INSERT INTO email_audit (employee_id, old_email, new_email, change_date)
VALUES (:OLD.employee_id, :OLD.email, :NEW.email, SYSDATE);
END IF;
END;
/
79. Trigger to Enforce Minimum Age for Employees
CREATE OR REPLACE TRIGGER trg_enforce_minimum_age
BEFORE INSERT OR UPDATE OF date_of_birth ON employees
FOR EACH ROW
DECLARE
age INTEGER;
BEGIN
age := TRUNC(MONTHS_BETWEEN(SYSDATE, :NEW.date_of_birth) / 12);
IF age < 21 THEN
RAISE_APPLICATION_ERROR(-20038, 'Employee must be at least 21 years old.');
END IF;
END;
/
80. Trigger to Log Changes to Status
CREATE OR REPLACE TRIGGER trg_log_status_changes
AFTER UPDATE OF status ON employees
FOR EACH ROW
BEGIN
IF :NEW.status <> :OLD.status THEN
INSERT INTO status_audit (employee_id, old_status, new_status, change_date)
VALUES (:OLD.employee_id, :OLD.status, :NEW.status, SYSDATE);
END IF;
END;
/
81. Trigger to Prevent Updates to Manager ID
CREATE OR REPLACE TRIGGER trg_prevent_manager_id_update
BEFORE UPDATE OF manager_id ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'MANAGER' THEN
RAISE_APPLICATION_ERROR(-20039, 'Manager ID cannot be updated.');
END IF;
END;
/
82. Trigger to Automatically Assign Default Manager
CREATE OR REPLACE TRIGGER trg_auto_assign_default_manager
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.manager_id IS NULL THEN
:NEW.manager_id := 100; -- Default Manager ID
END IF;
END;
/
83. Trigger to Log Salary Changes
CREATE OR REPLACE TRIGGER trg_log_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
INSERT INTO salary_audit_log (employee_id, old_salary, new_salary, change_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/
84. Trigger to Prevent Insert if Department is Full
CREATE OR REPLACE TRIGGER trg_prevent_insert_if_department_full
BEFORE INSERT ON employees
FOR EACH ROW
DECLARE
department_count INTEGER;
BEGIN
SELECT COUNT(*) INTO department_count
FROM employees
WHERE department_id = :NEW.department_id;
IF department_count >= 50 THEN
RAISE_APPLICATION_ERROR(-20040, 'Department is full.');
END IF;
END;
/
85. Trigger to Enforce Maximum Salary Based on Job ID
CREATE OR REPLACE TRIGGER trg_enforce_max_salary_based_on_job_id
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id = 'EMPLOYEE' AND :NEW.salary > 5000 THEN
RAISE_APPLICATION_ERROR(-20041, 'Salary for employees cannot exceed 5000.');
END IF;
END;
/
86. Trigger to Log Department Changes
CREATE OR REPLACE TRIGGER trg_log_department_changes
AFTER UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id <> :OLD.department_id THEN
INSERT INTO department_audit (employee_id, old_department_id, new_department_id, change_date)
VALUES (:OLD.employee_id, :OLD.department_id, :NEW.department_id, SYSDATE);
END IF;
END;
/
87. Trigger to Restrict Deletion of Employees with Active Assignments
CREATE OR REPLACE TRIGGER trg_restrict_deletion_of_active_assignments
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.status = 'ACTIVE' THEN
RAISE_APPLICATION_ERROR(-20042, 'Employees with active assignments cannot be deleted.');
END IF;
END;
/
88. Trigger to Automatically Set Hire Date
CREATE OR REPLACE TRIGGER trg_auto_set_hire_date
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.hire_date IS NULL THEN
:NEW.hire_date := SYSDATE;
END IF;
END;
/
89. Trigger to Log Updates to Manager ID
CREATE OR REPLACE TRIGGER trg_log_manager_id_updates
AFTER UPDATE OF manager_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.manager_id <> :OLD.manager_id THEN
INSERT INTO manager_audit (employee_id, old_manager_id, new_manager_id, change_date)
VALUES (:OLD.employee_id, :OLD.manager_id, :NEW.manager_id, SYSDATE);
END IF;
END;
/
90. Trigger to Restrict Salary Decrease
CREATE OR REPLACE TRIGGER trg_restrict_salary_decrease
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < :OLD.salary THEN
RAISE_APPLICATION_ERROR(-20043, 'Salary cannot be decreased.');
END IF;
END;
/
91. Trigger to Enforce Unique Combination of Name and DOB
CREATE OR REPLACE TRIGGER trg_enforce_unique_name_dob_combination
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
DECLARE
name_dob_count INTEGER;
BEGIN
SELECT COUNT(*) INTO name_dob_count
FROM employees
WHERE first_name = :NEW.first_name AND last_name = :NEW.last_name AND date_of_birth = :NEW.date_of_birth AND employee_id != :NEW.employee_id;
IF name_dob_count > 0 THEN
RAISE_APPLICATION_ERROR(-20044, 'Combination of first name, last name, and date of birth must be unique.');
END IF;
END;
/
92. Trigger to Log Email Changes
CREATE OR REPLACE TRIGGER trg_log_email_changes
AFTER UPDATE OF email ON employees
FOR EACH ROW
BEGIN
INSERT INTO email_audit_log (employee_id, old_email, new_email, change_date)
VALUES (:OLD.employee_id, :OLD.email, :NEW.email, SYSDATE);
END;
/
93. Trigger to Restrict Updates to Managers
CREATE OR REPLACE TRIGGER trg_restrict_updates_to_managers
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.job_id = 'MANAGER' THEN
RAISE_APPLICATION_ERROR(-20045, 'Managers cannot be updated.');
END IF;
END;
/
94. Trigger to Log Phone Number Changes
CREATE OR REPLACE TRIGGER trg_log_phone_number_changes
AFTER UPDATE OF phone_number ON employees
FOR EACH ROW
BEGIN
INSERT INTO phone_number_audit_log (employee_id, old_phone_number, new_phone_number, change_date)
VALUES (:OLD.employee_id, :OLD.phone_number, :NEW.phone_number, SYSDATE);
END;
/
95. Trigger to Prevent Update if Department is Full
CREATE OR REPLACE TRIGGER trg_prevent_update_if_department_full
BEFORE UPDATE ON employees
FOR EACH ROW
DECLARE
department_count INTEGER;
BEGIN
SELECT COUNT(*) INTO department_count
FROM employees
WHERE department_id = :NEW.department_id;
IF department_count >= 100 THEN
RAISE_APPLICATION_ERROR(-20046, 'Department is full.');
END IF;
END;
/
96. Trigger to Enforce Minimum Salary
CREATE OR REPLACE TRIGGER trg_enforce_minimum_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 3000 THEN
RAISE_APPLICATION_ERROR(-20047, 'Salary must be at least 3000.');
END IF;
END;
/
97. Trigger to Log Updates to Full Name
CREATE OR REPLACE TRIGGER trg_log_full_name_updates
AFTER UPDATE OF first_name, last_name ON employees
FOR EACH ROW
BEGIN
INSERT INTO full_name_audit_log (employee_id, old_full_name, new_full_name, change_date)
VALUES (:OLD.employee_id, :OLD.first_name || ' ' || :OLD.last_name, :NEW.first_name || ' ' || :NEW.last_name, SYSDATE);
END;
/
98. Trigger to Prevent Update if Employee is Inactive
CREATE OR REPLACE TRIGGER trg_prevent_update_if_inactive
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.status = 'INACTIVE' THEN
RAISE_APPLICATION_ERROR(-20048, 'Inactive employees cannot be updated.');
END IF;
END;
/
99. Trigger to Enforce Salary Cap Based on Job ID
CREATE OR REPLACE TRIGGER trg_enforce_salary_cap_based_on_job_id
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.job_id = 'EXECUTIVE' AND :NEW.salary > 20000 THEN
RAISE_APPLICATION_ERROR(-20049, 'Salary for executives cannot exceed 20000.');
END IF;
END;
/
100. Trigger to Log Department ID Changes
CREATE OR REPLACE TRIGGER trg_log_department_id_changes
AFTER UPDATE OF department_id ON employees
FOR EACH ROW
BEGIN
IF :NEW.department_id <> :OLD.department_id THEN
INSERT INTO department_id_audit_log (employee_id, old_department_id, new_department_id, change_date)
VALUES (:OLD.employee_id, :OLD.department_id, :NEW.department_id, SYSDATE);
END IF;
END;
/
No comments:
Post a Comment