Basic INSERT Triggers
1. Insert Trigger for Audit Table
Description: This trigger writes an audit record after a new employee row is inserted.
CREATE OR REPLACE TRIGGER audit_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
INSERT INTO audit_log (operation, table_name, row_id, changed_at)
VALUES ('INSERT', 'employees', :NEW.employee_id, SYSDATE);
END;
/2. Insert Trigger to Auto-Populate Fields
Description: This trigger sets created date and created by values automatically before insert.
CREATE OR REPLACE TRIGGER auto_populate_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.created_date := SYSDATE;
:NEW.created_by := USER;
END;
/3. Insert Trigger to Validate Data
Description: This trigger prevents inserting employees with a negative salary.
CREATE OR REPLACE TRIGGER validate_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
END IF;
END;
/Basic UPDATE Triggers
4. Update Trigger to Log Changes
Description: This trigger stores old and new salary values after an employee salary update.
CREATE OR REPLACE TRIGGER log_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO update_log (employee_id, old_salary, new_salary, changed_at)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/5. Update Trigger to Prevent Changes
Description: This trigger blocks salary increases during update.
CREATE OR REPLACE TRIGGER prevent_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
IF :OLD.salary < :NEW.salary THEN
RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be increased');
END IF;
END;
/6. Update Trigger to Automatically Update Timestamps
Description: This trigger refreshes the last updated timestamp before an employee row is updated.
CREATE OR REPLACE TRIGGER update_timestamp
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.last_updated := SYSDATE;
END;
/Basic DELETE Triggers
7. Delete Trigger to Move Data to Archive Table
Description: This trigger copies deleted employee rows into an archive table.
CREATE OR REPLACE TRIGGER archive_on_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO archived_employees (employee_id, name, salary, deleted_at)
VALUES (:OLD.employee_id, :OLD.name, :OLD.salary, SYSDATE);
END;
/8. Delete Trigger to Prevent Deletion
Description: This trigger stops all delete operations on employees.
CREATE OR REPLACE TRIGGER prevent_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
RAISE_APPLICATION_ERROR(-20003, 'Deletion is not allowed');
END;
/9. Delete Trigger to Clean Up Related Data
Description: This trigger removes related employee project records after an employee is deleted.
CREATE OR REPLACE TRIGGER cleanup_on_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
DELETE FROM employee_projects WHERE employee_id = :OLD.employee_id;
END;
/Compound Triggers
10. Compound Trigger for Handling Multiple Actions
Description: This trigger shows how to place logic in statement-level and row-level sections in one trigger.
CREATE OR REPLACE TRIGGER compound_trigger_example
FOR INSERT OR UPDATE OR DELETE ON employees
COMPOUND TRIGGER
BEFORE_STATEMENT IS
BEGIN
-- Actions before statement
END BEFORE_STATEMENT;
AFTER STATEMENT IS
BEGIN
-- Actions after statement
END AFTER_STATEMENT;
AFTER EACH ROW IS
BEGIN
-- Actions after each row
END AFTER EACH ROW;
END;
/Triggers with PL/SQL Block
11. Trigger with Exception Handling
Description: This trigger demonstrates exception handling and logs the error message into an error table.
CREATE OR REPLACE TRIGGER example_trigger
AFTER INSERT ON employees
FOR EACH ROW
DECLARE
v_message VARCHAR2(100);
BEGIN
BEGIN
-- Trigger logic
EXCEPTION
WHEN OTHERS THEN
v_message := 'Error: ' || SQLERRM;
INSERT INTO error_log (error_message, error_date)
VALUES (v_message, SYSDATE);
END;
END;
/Triggers on Views
12. Insert Trigger on a View
Description: This INSTEAD OF trigger lets users insert into a view by redirecting data into the base table.
CREATE OR REPLACE TRIGGER view_insert_trigger
INSTEAD OF INSERT ON emp_view
FOR EACH ROW
BEGIN
INSERT INTO employees (employee_id, name, salary)
VALUES (:NEW.employee_id, :NEW.name, :NEW.salary);
END;
/13. Update Trigger on a View
Description: This INSTEAD OF trigger redirects view updates to the underlying employees table.
CREATE OR REPLACE TRIGGER view_update_trigger
INSTEAD OF UPDATE ON emp_view
FOR EACH ROW
BEGIN
UPDATE employees
SET name = :NEW.name, salary = :NEW.salary
WHERE employee_id = :OLD.employee_id;
END;
/Advanced Triggers
14. Trigger to Manage Sequence Values
Description: This trigger assigns the next sequence value to the employee ID before insert.
CREATE OR REPLACE TRIGGER manage_sequence
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.employee_id := employees_seq.NEXTVAL;
END;
/15. Trigger to Calculate and Update Totals
Description: This trigger recalculates the order total after row changes in order_items.
CREATE OR REPLACE TRIGGER update_totals
AFTER INSERT OR UPDATE ON order_items
FOR EACH ROW
BEGIN
UPDATE orders
SET total_amount = (SELECT SUM(item_price * quantity) FROM order_items WHERE order_id = :NEW.order_id)
WHERE order_id = :NEW.order_id;
END;
/Triggers for Error Handling
16. Trigger to Log Errors
Description: This trigger simulates an error and writes the error message into an error log table.
CREATE OR REPLACE TRIGGER log_error_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
-- Custom error handling logic
BEGIN
-- Simulate an error
RAISE_APPLICATION_ERROR(-20004, 'Custom error message');
EXCEPTION
WHEN OTHERS THEN
INSERT INTO error_log (error_message, error_date)
VALUES (SQLERRM, SYSDATE);
END;
END;
/DML Triggers
17. Trigger to Calculate Discounts
Description: This trigger calculates a discount automatically based on the order total amount.
CREATE OR REPLACE TRIGGER calculate_discount
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
IF :NEW.total_amount > 1000 THEN
:NEW.discount := :NEW.total_amount * 0.1;
ELSE
:NEW.discount := 0;
END IF;
END;
/18. Trigger to Synchronize Data Across Tables
Description: This trigger updates the department manager name when an employee changes.
CREATE OR REPLACE TRIGGER sync_data
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
UPDATE departments
SET manager_name = :NEW.name
WHERE department_id = :NEW.department_id;
END;
/Triggers with Custom Functions
19. Trigger Using a Custom Function
Description: This trigger calls a custom salary calculation function during insert.
CREATE OR REPLACE TRIGGER trigger_with_function
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.salary := calculate_salary(:NEW.base_salary);
END;
/20. Trigger to Call Another Procedure
Description: This trigger calls a stored procedure after a new employee is inserted.
CREATE OR REPLACE TRIGGER call_procedure_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
log_employee_insert(:NEW.employee_id, :NEW.name);
END;
/Row-Level and Statement-Level Triggers
21. Row-Level Trigger with Detailed Logging
Description: This trigger logs whether the change was an insert, update, or delete for each row.
CREATE OR REPLACE TRIGGER detailed_log_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO detailed_log (operation, employee_id, name, operation_date)
VALUES (
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
:OLD.employee_id,
:NEW.name,
SYSDATE
);
END;
/22. Statement-Level Trigger to Monitor Execution Time
Description: This trigger prints a message after an insert statement finishes running.
CREATE OR REPLACE TRIGGER monitor_execution_time
AFTER INSERT ON employees
BEGIN
DBMS_OUTPUT.PUT_LINE('Insert statement executed at: ' || SYSDATE);
END;
/Triggers for Data Integrity
23. Trigger to Ensure Referential Integrity
Description: This trigger blocks inserts or updates when the department ID does not exist.
CREATE OR REPLACE TRIGGER check_ref_integrity
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM departments WHERE department_id = :NEW.department_id) THEN
RAISE_APPLICATION_ERROR(-20005, 'Invalid department ID');
END IF;
END;
/24. Trigger to Enforce Business Rules
Description: This trigger enforces a minimum salary business rule before insert or update.
CREATE OR REPLACE TRIGGER enforce_business_rules
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 20000 THEN
RAISE_APPLICATION_ERROR(-20006, 'Salary must be at least 20,000');
END IF;
END;
/Complex Trigger Logic
25. Trigger with Multiple Conditions
Description: This trigger checks whether salary is below zero or above the maximum allowed amount.
CREATE OR REPLACE TRIGGER multi_condition_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary < 0 THEN
RAISE_APPLICATION_ERROR(-20007, 'Salary cannot be negative');
ELSIF :NEW.salary > 100000 THEN
RAISE_APPLICATION_ERROR(-20008, 'Salary cannot exceed 100,000');
END IF;
END;
/26. Trigger to Handle Multiple Tables
Description: This trigger writes to a log table and updates departments after employee changes.
CREATE OR REPLACE TRIGGER multi_table_trigger
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_log (employee_id, log_date, log_message)
VALUES (:NEW.employee_id, SYSDATE, 'Employee data updated');
UPDATE departments
SET last_updated = SYSDATE
WHERE department_id = :NEW.department_id;
END;
/Triggers for Historical Tracking
27. Trigger to Track Historical Changes
Description: This trigger stores old and new salary values every time an employee row is updated.
CREATE OR REPLACE TRIGGER history_tracking_trigger
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
INSERT INTO employee_history (employee_id, old_salary, new_salary, update_date)
VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/28. Trigger to Maintain Revision History
Description: This trigger stores order revisions whenever the total amount changes.
CREATE OR REPLACE TRIGGER revision_history_trigger
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
INSERT INTO order_revisions (order_id, old_amount, new_amount, revision_date)
VALUES (:OLD.order_id, :OLD.total_amount, :NEW.total_amount, SYSDATE);
END;
/Triggers with Bulk Processing
29. Bulk Processing with Triggers
Description: This trigger is a placeholder for bulk processing logic such as collection-based operations.
CREATE OR REPLACE TRIGGER bulk_process_trigger
AFTER INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
-- Use bulk operations to process data
-- For example, using DBMS_SQL or PL/SQL tables
END;
/30. Trigger to Handle Multiple Inserts
Description: This trigger inserts several task rows for each new employee.
CREATE OR REPLACE TRIGGER multi_insert_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
FOR i IN 1..5 LOOP
INSERT INTO employee_tasks (employee_id, task_id, task_date)
VALUES (:NEW.employee_id, i, SYSDATE);
END LOOP;
END;
/Triggers for Logging and Monitoring
31. Trigger to Monitor Changes
Description: This trigger logs insert, update, and delete operations into a change log table.
CREATE OR REPLACE TRIGGER change_monitoring_trigger
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO change_log (operation, employee_id, operation_date)
VALUES (
CASE
WHEN INSERTING THEN 'INSERT'
WHEN UPDATING THEN 'UPDATE'
WHEN DELETING THEN 'DELETE'
END,
:OLD.employee_id,
SYSDATE
);
END;
/32. Trigger to Log User Actions
Description: This trigger logs user actions from the employee_actions table into an audit table.
CREATE OR REPLACE TRIGGER user_action_log
AFTER INSERT ON employee_actions
FOR EACH ROW
BEGIN
INSERT INTO user_actions_log (user_id, action_type, action_date)
VALUES (:NEW.user_id, :NEW.action_type, SYSDATE);
END;
/Triggers for Conditional Logic
33. Trigger with Conditional Logic
Description: This trigger logs employees whose salary is above 50000.
CREATE OR REPLACE TRIGGER conditional_logic_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 50000 THEN
INSERT INTO high_salary_log (employee_id, salary, log_date)
VALUES (:NEW.employee_id, :NEW.salary, SYSDATE);
END IF;
END;
/34. Trigger to Handle Different Roles
Description: This trigger logs employees with the Manager role.
CREATE OR REPLACE TRIGGER role_based_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.role = 'Manager' THEN
INSERT INTO manager_log (employee_id, log_date)
VALUES (:NEW.employee_id, SYSDATE);
END IF;
END;
/Triggers for User Interaction
35. Trigger to Send Notifications
Description: This trigger calls a notification procedure after a new order is inserted.
CREATE OR REPLACE TRIGGER send_notification_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
-- Call a procedure to send notifications
send_order_notification(:NEW.order_id);
END;
/36. Trigger to Generate Reports
Description: This trigger calls a reporting procedure after a new employee is inserted.
CREATE OR REPLACE TRIGGER report_generation_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
-- Generate a report
generate_employee_report(:NEW.employee_id);
END;
/Complex Logic with Triggers
37. Trigger with Nested Logic
Description: This trigger adds a row to the high salary report when salary is greater than 60000.
CREATE OR REPLACE TRIGGER nested_logic_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 60000 THEN
BEGIN
INSERT INTO high_salary_report (employee_id, salary, report_date)
VALUES (:NEW.employee_id, :NEW.salary, SYSDATE);
END;
END IF;
END;
/38. Trigger for Cross-Table Validation
Description: This trigger prevents insert if the department ID does not exist.
CREATE OR REPLACE TRIGGER cross_table_validation_trigger
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
IF NOT EXISTS (SELECT 1 FROM departments WHERE department_id = :NEW.department_id) THEN
RAISE_APPLICATION_ERROR(-20009, 'Department ID does not exist');
END IF;
END;
/Triggers for Data Aggregation
39. Trigger to Aggregate Data
Description: This trigger updates daily sales totals whenever a new sale is inserted.
CREATE OR REPLACE TRIGGER aggregate_data_trigger
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
UPDATE sales_summary
SET total_sales = (SELECT SUM(sale_amount) FROM sales WHERE sale_date = :NEW.sale_date)
WHERE sale_date = :NEW.sale_date;
END;
/40. Trigger to Update Statistics
Description: This trigger refreshes product statistics after inserts or updates in products.
CREATE OR REPLACE TRIGGER update_statistics_trigger
AFTER INSERT OR UPDATE ON products
FOR EACH ROW
BEGIN
UPDATE product_statistics
SET total_units = (SELECT SUM(units_sold) FROM sales WHERE product_id = :NEW.product_id)
WHERE product_id = :NEW.product_id;
END;
/Triggers for Complex Operations
41. Trigger to Perform Complex Calculation
Description: This trigger calculates the total amount using quantity and a custom price function.
CREATE OR REPLACE TRIGGER complex_calculation_trigger
AFTER INSERT OR UPDATE ON orders
FOR EACH ROW
BEGIN
:NEW.total_amount := :NEW.quantity * calculate_price(:NEW.product_id);
END;
/42. Trigger to Synchronize Data Between Tables
Description: This trigger updates the order summary when an order changes.
CREATE OR REPLACE TRIGGER data_synchronization_trigger
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
UPDATE order_summary
SET total_amount = (SELECT SUM(total_amount) FROM orders WHERE order_id = :NEW.order_id)
WHERE order_id = :NEW.order_id;
END;
/Triggers for Date and Time Handling
43. Trigger to Manage Dates
Description: This trigger validates that event dates are not in the past.
CREATE OR REPLACE TRIGGER manage_dates_trigger
AFTER INSERT ON events
FOR EACH ROW
BEGIN
IF :NEW.event_date < SYSDATE THEN
RAISE_APPLICATION_ERROR(-20010, 'Event date cannot be in the past');
END IF;
END;
/44. Trigger to Handle Expiry Dates
Description: This trigger blocks products with expired dates from being inserted or updated.
CREATE OR REPLACE TRIGGER handle_expiry_dates
BEFORE INSERT OR UPDATE ON products
FOR EACH ROW
BEGIN
IF :NEW.expiry_date < SYSDATE THEN
RAISE_APPLICATION_ERROR(-20011, 'Product has expired');
END IF;
END;
/Triggers for Data Transformation
45. Trigger to Transform Data
Description: This trigger converts employee names to uppercase before saving them.
CREATE OR REPLACE TRIGGER data_transformation_trigger
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.employee_name := UPPER(:NEW.employee_name);
END;
/46. Trigger to Standardize Data
Description: This trigger converts email addresses to lowercase before saving them.
CREATE OR REPLACE TRIGGER standardize_data_trigger
BEFORE INSERT OR UPDATE ON customers
FOR EACH ROW
BEGIN
:NEW.email_address := LOWER(:NEW.email_address);
END;
/Triggers for Security and Access Control
47. Trigger to Control Access
Description: This trigger prevents inserts unless the current user is admin.
CREATE OR REPLACE TRIGGER control_access_trigger
BEFORE INSERT ON sensitive_data
FOR EACH ROW
BEGIN
IF USER <> 'admin' THEN
RAISE_APPLICATION_ERROR(-20012, 'Access denied');
END IF;
END;
/48. Trigger to Mask Sensitive Information
Description: This trigger replaces sensitive SSN values with masked text before saving.
CREATE OR REPLACE TRIGGER mask_sensitive_info
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
:NEW.social_security_number := 'XXX-XX-XXXX';
END;
/Triggers for Synchronization
49. Trigger for Data Synchronization Across Systems
Description: This trigger calls an external synchronization procedure after inserting an employee.
CREATE OR REPLACE TRIGGER data_sync_trigger
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
-- Call external API to synchronize data
sync_employee_data(:NEW.employee_id);
END;
/50. Trigger for Inter-System Communication
Description: This trigger notifies another system when an order is updated.
CREATE OR REPLACE TRIGGER inter_system_communication_trigger
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
-- Notify another system about the update
notify_system_of_order_update(:NEW.order_id);
END;
/Note: Some example triggers are illustrative and may need small adjustments before running in a real Oracle schema.
No comments:
Post a Comment