Programming SQL Server Database Triggers and Functions

Focused View

Ryan Booz

3:20:07

50 View

1.Course Overview

01.Course Overview.mp4

01:36

2.Validating and Modifying Data with DML Triggers

02.Overview.mp4

00:48

03.What Is a DML Trigger.mp4

02:01

04.Understanding AFTER vs. INSTEAD OF Triggers.mp4

04:52

05.Understanding the anatomy of DML Triggers.mp4

05:26

06.Utilizing the INSERTED and DELETED Virtual Tables.mp4

04:22

07.Common Use Cases for DML Triggers.mp4

02:22

08.Using INSERT AFTER Triggers.mp4

01:01

09.Demo - Creating an AFTER INSERT Trigger.mp4

06:04

10.Using INSTEAD OF Triggers.mp4

01:17

11.Demo - Creating an INSTEAD OF Trigger.mp4

03:11

12.Demo - Correcting Bad Data with INSTEAD OF Triggers.mp4

03:10

13.Demo - Updating Data through a View.mp4

03:25

14.Using DELETE Triggers.mp4

01:03

15.Demo - Protecting Data with DELETE Triggers.mp4

01:03

16.Demo - Creating an Audit Log with DELETE Triggers.mp4

03:43

17.Reacting to Modified Data in UPDATE Triggers.mp4

01:29

18.Demo - Logging Changes with UPDATE Triggers.mp4

03:42

19.Modifying Trigger Execution Order.mp4

00:52

20.Demo - Setting Trigger Order.mp4

02:43

21.Demo - Ordering Triggers for Correct Results.mp4

05:07

22.Review.mp4

00:52

3.Protecting the Database with DDL and Logon Triggers

23.Overview.mp4

00:28

24.What Is a DDL Trigger.mp4

01:21

25.Understanding the Anatomy of DDL Triggers.mp4

02:13

26.Examining DDL Trigger Events and Event Groups.mp4

01:33

27.Common Use Cases for DDL Triggers.mp4

00:38

28.Using DDL Triggers to Audit Events and Prevent Changes.mp4

00:19

29.Demo - Preventing Schema Changes with DDL Triggers.mp4

02:11

30.Demo - Logging Schema Changes with DDL Triggers.mp4

02:36

31.What Is a LOGON Trigger.mp4

01:32

32.Understanding the Anatomy of LOGON Triggers.mp4

02:44

33.Common Use Cases for LOGON Triggers.mp4

01:21

34.Demo - Preventing Connections with LOGON Triggers.mp4

03:38

35.Demo - Logging Authentications with LOGON Triggers.mp4

03:15

36.Review.mp4

00:44

4.Working Smarter with Triggers

37.Overview.mp4

00:51

38.Trigger Security.mp4

05:16

39.The Problem with MERGE and Triggers.mp4

01:41

40.Demo - MERGE and Triggers.mp4

05:45

41.Bypassing Transactions in Triggers.mp4

02:20

42.Demo - Logging Information Outside of the Transaction.mp4

04:29

43.Triggers in Moderation.mp4

02:45

44.Demo - Mitigating Extra Work in Triggers.mp4

04:51

45.Improving Performance with Service Broker.mp4

03:22

46.Demo - Using Service Broker in Triggers.mp4

06:01

47.Summary.mp4

00:54

5.Reusing Code with Functions

48.Overview.mp4

01:02

49.What Are SQL Server Functions.mp4

04:29

50.Why Are Functions Useful.mp4

03:04

51.Deterministic vs. Non-deterministic Functions.mp4

01:38

52.Multi-statement vs. Inline Table-valued Functions.mp4

07:03

53.Summary.mp4

00:52

6.Scaler and Table-valued Functions

54.Overview.mp4

01:02

55.A Closer Look at Multi-statement Functions.mp4

05:06

56.Demo - Multi-statement Scalar Functions.mp4

05:06

57.Demo - Multi-Statement Table-valued Functions.mp4

04:29

58.Challenges with Multi-statement Functions.mp4

02:50

59.Demo - Estimation Problems in Multi-statement Functions.mp4

04:33

60.Improvements in SQL Server 2017 and 2019.mp4

04:53

61.Summary.mp4

00:41

7.Improving Function Performance with Inline Table-valued Functions

62.Overview.mp4

00:56

63.A Closer Look at Inline Table-valued Functions.mp4

02:34

64.Demo - Creating Inline Table-valued Functions.mp4

02:32

65.Converting Multi-statement to Inline Table-valued Functions.mp4

01:15

66.Demo - Converting a MSTVF into an ITVF.mp4

05:14

67.Demo - Improving Performance with ITVFs.mp4

05:56

68.Avoiding Parameter Sniffing in ITVFs.mp4

05:26

69.Demo - Identifying Parameter Sniffing.mp4

05:45

70.Summary.mp4

00:44

Description

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.

More details

User Reviews

Rating

average 0

Total votes0

Focused display

SQL Server

Database Programming

Microsoft SQL

Ryan Booz

Instructor's Courses

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 with MySQL and PostgreSQL in the late 90s, and has been working with SQL Server since 2004. He’s at the top of his game when he’s learning something new about the data platform or teaching others about the technology he loves.

Pluralsight

View courses Pluralsight

Pluralsight, LLC is an American privately held online education company that offers a variety of video training courses for software developers, IT administrators, and creative professionals through its website. Founded in 2004 by Aaron Skonnard, Keith Brown, Fritz Onion, and Bill Williams, the company has its headquarters in Farmington, Utah. As of July 2018, it uses more than 1,400 subject-matter experts as authors, and offers more than 7,000 courses in its catalog. Since first moving its courses online in 2007, the company has expanded, developing a full enterprise platform, and adding skills assessment modules.