SQL Server Database Triggers

Introduction

• Automate SQL Server with triggers
• What you should know
• Make the most of the exercise files

1. Trigger Fundamentals

• What are SQL Server triggers?
• Two triggers types: INSTEAD OF and AFTER
• Create a trigger to print a message
• Timestamp a table modification
• Firing INSTEAD OF triggers
• Challenge: Create a trigger
• Solution: Create a trigger

2. Work with Modified Data

• Read values modified by a trigger
• Using the inserted logical table
• Using the deleted logical table

3. Database- and Server-Level Triggers

• Triggers on CREATE, ALTER, or DROP
• Rollback database modifications
• Reading the EVENTDATA() XML
• Writing data from the EVENTDATA() function
• Challenge: Create a DDL trigger
• Solution: Create a DDL trigger

4. Trigger Management

• View all triggers in a database
• Enable and disable triggers
• Disable nested triggers
• Recursive triggers
• Reorder trigger execution

5. Project: Managing Inventory

• Creating triggers to maintain inventory
• Counting shipments received
• Subtract items sold
• Reorder items when low

Conclusion

• Next steps