db 2 Database Loose Coupling With Triggers

Описание: This video, titled "db 2 loose couple," provides a technical tutorial on implementing loose coupling in database systems using SQL Server triggers and scheduled jobs.

The core objective is to automate workflows between two departments (Trading and Shipping/Logistics) without allowing the secondary process to interfere with the performance or stability of the primary system.

Core Problem & Concept
The "Trading" system is treated as a "black box" with no API. The "Shipping" department needs to react to new orders.

Tight Coupling (The Risk): If a trigger runs complex logic or sends emails directly during an order save, any error or delay will slow down or crash the Trading system [01:32].

Loose Coupling (The Solution): A trigger simply logs the event (e.g., "New Order") into a dedicated log table. A separate, scheduled job then processes these logs in batches [01:51].

Key Technical Components
DML Triggers: The video explains AFTER and INSTEAD OF triggers [02:31].

It highlights the use of the inserted and deleted logical tables to capture data changes [03:52].

The Log Table (order_log): This table tracks event IDs, types, order IDs, and the processing status (e.g., 0 for pending, 2 for success) [09:31].

The Trigger: A sample trigger tr_log_order is shown. It detects if an order was inserted or if specific fields (like ShipAddress) were updated and records these events in the log [10:24].

The Processor (sp_order_process): This stored procedure uses a cursor to iterate through unprocessed logs [15:38].

It executes the necessary business logic (like updating inventory) and records the result [17:17].

Why Use This Approach?
Error Isolation: If the inventory update fails, it only marks the log entry as "failed" rather than rolling back the actual order in the Trading system [09:05].

Performance: The trigger is extremely fast because it only performs a simple insert into a log table, minimizing the impact on the user saving the order [01:39].

Audit Trail: The log table provides a natural history of all processed and failed events [10:08].

Demonstration Highlights
Tight Coupling Failure: The video shows a "bad" trigger that causes a divide-by-zero error, which successfully crashes the original INSERT statement and prevents the order from being saved [08:35].

Loose Coupling Success: The video demonstrates how the system logs an event, fails the processing step internally, but still allows the original order to remain safely in the database [19:15].