Programming SQL Server Database Triggers and Functions

Programming SQL Server Database Triggers and Functions

English | MP4 | AVC 1280×720 | AAC 44KHz 2ch | 3h 20m | 446 MB

SQL Server Triggers and Functions can be powerful tools for the DBA. In this course we’ll demonstrate best practices, provide solutions to common problems, and identify when Triggers and Functions are the best choice for your requirements.

As a SQL Server Database Developer, it is often necessary to have business-layer logic that exists within the database to ensure clean, consistent data. Although constraints and foreign key relationships can go a long way to provide the essential relational guarantees, Triggers provide the ability to validate and act on data at the time of modification, while Functions help you provide consistent methods for accessing the data across applications. In this course, Programming SQL Server Triggers and Functions, you will learn foundational knowledge to better administer and develop efficient, high-performing Triggers and Functions. First, you will learn how to develop DML, DDL, and LOGON Triggers, and how to avoid common mistakes. Next, you will discover the differences between Multi-Statement and Single-Statement functions and how to write the most efficient functions possible. Finally, you will explore some lesser-known features and upcoming improvements in newer versions of SQL Server that can improve your overall experience with Triggers and Functions. When you are finished with this course you will have the skills and knowledge needed to administer and develop efficient, high-performing Triggers and Functions.

Table of Contents

Course Overview
1 Course Overview

Validating and Modifying Data with DML Triggers
2 Overview
3 What Is a DML Trigger
4 Understanding AFTER vs. INSTEAD OF Triggers
5 Understanding the anatomy of DML Triggers
6 Utilizing the INSERTED and DELETED Virtual Tables
7 Common Use Cases for DML Triggers
8 Using INSERT AFTER Triggers
9 Demo – Creating an AFTER INSERT Trigger
10 Using INSTEAD OF Triggers
11 Demo – Creating an INSTEAD OF Trigger
12 Demo – Correcting Bad Data with INSTEAD OF Triggers
13 Demo – Updating Data through a View
14 Using DELETE Triggers
15 Demo – Protecting Data with DELETE Triggers
16 Demo – Creating an Audit Log with DELETE Triggers
17 Reacting to Modified Data in UPDATE Triggers
18 Demo – Logging Changes with UPDATE Triggers
19 Modifying Trigger Execution Order
20 Demo – Setting Trigger Order
21 Demo – Ordering Triggers for Correct Results
22 Review

Protecting the Database with DDL and Logon Triggers
23 Overview
24 What Is a DDL Trigger
25 Understanding the Anatomy of DDL Triggers
26 Examining DDL Trigger Events and Event Groups
27 Common Use Cases for DDL Triggers
28 Using DDL Triggers to Audit Events and Prevent Changes
29 Demo – Preventing Schema Changes with DDL Triggers
30 Demo – Logging Schema Changes with DDL Triggers
31 What Is a LOGON Trigger
32 Understanding the Anatomy of LOGON Triggers
33 Common Use Cases for LOGON Triggers
34 Demo – Preventing Connections with LOGON Triggers
35 Demo – Logging Authentications with LOGON Triggers
36 Review

Working Smarter with Triggers
37 Overview
38 Trigger Security
39 The Problem with MERGE and Triggers
40 Demo – MERGE and Triggers
41 Bypassing Transactions in Triggers
42 Demo – Logging Information Outside of the Transaction
43 Triggers in Moderation
44 Demo – Mitigating Extra Work in Triggers
45 Improving Performance with Service Broker
46 Demo – Using Service Broker in Triggers
47 Summary

Reusing Code with Functions
48 Overview
49 What Are SQL Server Functions
50 Why Are Functions Useful
51 Deterministic vs. Non-deterministic Functions
52 Multi-statement vs. Inline Table-valued Functions
53 Summary

Scaler and Table-valued Functions
54 Overview
55 A Closer Look at Multi-statement Functions
56 Demo – Multi-statement Scalar Functions
57 Demo – Multi-Statement Table-valued Functions
58 Challenges with Multi-statement Functions
59 Demo – Estimation Problems in Multi-statement Functions
60 Improvements in SQL Server 2017 and 2019
61 Summary

Improving Function Performance with Inline Table-valued Functions
62 Overview
63 A Closer Look at Inline Table-valued Functions
64 Demo – Creating Inline Table-valued Functions
65 Converting Multi-statement to Inline Table-valued Functions
66 Demo – Converting a MSTVF into an ITVF
67 Demo – Improving Performance with ITVFs
68 Avoiding Parameter Sniffing in ITVFs
69 Demo – Identifying Parameter Sniffing
70 Summary