Title: SQL Server-XML-Lesson 9-Using trigger to raise an error when the user tries to delete a customer from the Customers table
Duration: 20 minutes
Summary: In this video, I am going to continue my discussion on triggers. I will wrap up with the instead of trigger. Using this trigger we will raise an error when the user tries to delete a customer from the Customers table. We will write the error message and change the status of the customer to ‘inactive’ instead. Then we move into discussing table-valued functions and cross-apply. First we created an Employees table and a Departments table. Then, we wrote a table-valued function that uses a CTE to create and return a table based on the manager id. The function will return the manager and all the employees reporting to him/her. Finally, we used a cross-apply and an outer-apply to see how we can join between the Departments table and the table-valued function.
Methodology of the development of example: Good coding standard and simplified design to illustrate the key points.
Technology Used: XML and SQL Server 2008
Keywords: table, database, primary key, trigger, raiserror, instead of trigger, drop trigger, inner join, table-valued function, returns, anchor section, recursive section, Common Table Expression (CTE), union all, cross apply, outer apply.