db 2 Database Loose Coupling With Triggers скачать в хорошем качестве

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].