Basic INSERT Triggers
=====================
1. Insert Trigger for Audit Table
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
Trigger with Custom Functions
==============================
19. Trigger Using a Custom Function
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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
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;
No comments:
Post a Comment