Programming SQL Server Database Triggers and Functions
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.
What you'll learn
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
- Version Check 0m
- Overview 1m
- What Is a DML Trigger? 2m
- Understanding AFTER vs. INSTEAD OF Triggers 5m
- Understanding the anatomy of DML Triggers 5m
- Utilizing the INSERTED and DELETED Virtual Tables 4m
- Common Use Cases for DML Triggers 2m
- Using INSERT AFTER Triggers 1m
- Demo: Creating an AFTER INSERT Trigger 6m
- Using INSTEAD OF Triggers 1m
- Demo: Creating an INSTEAD OF Trigger 3m
- Demo: Correcting Bad Data with INSTEAD OF Triggers 3m
- Demo: Updating Data through a View 3m
- Using DELETE Triggers 1m
- Demo: Protecting Data with DELETE Triggers 1m
- Demo: Creating an Audit Log with DELETE Triggers 4m
- Reacting to Modified Data in UPDATE Triggers 1m
- Demo: Logging Changes with UPDATE Triggers 4m
- Modifying Trigger Execution Order 1m
- Demo: Setting Trigger Order 3m
- Demo: Ordering Triggers for Correct Results 5m
- Review 1m
- Overview 0m
- What Is a DDL Trigger? 1m
- Understanding the Anatomy of DDL Triggers 2m
- Examining DDL Trigger Events and Event Groups 2m
- Common Use Cases for DDL Triggers 1m
- Using DDL Triggers to Audit Events and Prevent Changes 0m
- Demo: Preventing Schema Changes with DDL Triggers 2m
- Demo: Logging Schema Changes with DDL Triggers 3m
- What Is a LOGON Trigger? 2m
- Understanding the Anatomy of LOGON Triggers 3m
- Common Use Cases for LOGON Triggers 1m
- Demo: Preventing Connections with LOGON Triggers 4m
- Demo: Logging Authentications with LOGON Triggers 3m
- Review 1m
- Overview 1m
- Trigger Security 5m
- The Problem with MERGE and Triggers 2m
- Demo: MERGE and Triggers 6m
- Bypassing Transactions in Triggers 2m
- Demo: Logging Information Outside of the Transaction 4m
- Triggers in Moderation 3m
- Demo: Mitigating Extra Work in Triggers 5m
- Improving Performance with Service Broker 3m
- Demo: Using Service Broker in Triggers 6m
- Summary 1m
- Overview 1m
- A Closer Look at Multi-statement Functions 5m
- Demo: Multi-statement Scalar Functions 5m
- Demo: Multi-Statement Table-valued Functions 4m
- Challenges with Multi-statement Functions 3m
- Demo: Estimation Problems in Multi-statement Functions 5m
- Improvements in SQL Server 2017 and 2019 5m
- Summary 1m
- Overview 1m
- A Closer Look at Inline Table-valued Functions 3m
- Demo: Creating Inline Table-valued Functions 3m
- Converting Multi-statement to Inline Table-valued Functions 1m
- Demo: Converting a MSTVF into an ITVF 5m
- Demo: Improving Performance with ITVFs 6m
- Avoiding Parameter Sniffing in ITVFs 5m
- Demo: Identifying Parameter Sniffing 6m
- Summary 1m
About the author
Ryan is the Chief Software Architect at KCF Technologies where he leads development of SmartDiagnostics, an industry leading platform for real-time predictive maintenance used by many of the largest manufactures in the North America. He primarily focuses on .Net, SQL Server, PostgreSQL and a myriad of cloud-first technologies in the AWS cloud. Before coming to KCF Technologies, Ryan spent more than 14 years with EnergyCAP, Inc. leading both IT and development. He is a long-time DBA, starting wi... more