Join Martin Guidry for an in-depth discussion in this video Implementing logging on DELETE, part of SQL Server: Triggers, Stored Procedures, and Functions.
For our DELETE stored procedure, the rules we're going to implement is the…stored procedure can only delete one record at a time, and we must maintain a…log of who deleted and when.…So in order to maintain that log I'd like to create a new table that will be…used for that logging.…I have some code staged for that.…It's a three column table; one column for user name, the person who did the…deletion, one column for the ID that they deleted, and one column for the date.…I also have some codes staged for the stored procedure.…So starting at the top we have with CREATE PROCEDURE Students_Delete.…
This time we are only taking one parameter.…All I need is the ID of what you would like to delete.…Line number 2 there is new to us.…We're saying EXCUTE AS CALLER.…There are some context in which a stored procedure might execute as the…system, but in this one, one of my business requirements is to log who is running this.…So I am going to specifically saying EXCUTE AS CALLER.…Line 6 through 11 we check to make sure that this ID already exists and make…
- Comparing triggers, functions, and stored procedures
- Installing and configuring SQL Server
- Creating a stored procedure
- Returning data using data sets
- Creating user-defined functions
- Using "after," "instead," and nested triggers
- Modifying existing stored procedures
- Implementing logging on DELETE
- Choosing between T-SQL and CLR
- Executing a stored procedure
- Passing parameters
Skill Level Advanced
1. Getting Started
2. Installing SQL Server
Configuring your environment4m 53s
3. Stored Procedures
4. User-Defined Functions
6. Putting It All Together
7. Using CLR Assemblies
8. Exploring an ASP.NET Web Application
Next steps1m 43s
- Mark as unwatched
- Mark all as unwatched
Are you sure you want to mark all the videos in this course as unwatched?
This will not affect your course history, your reports, or your certificates of completion for this course.Cancel
Take notes with your new membership!
Type in the entry box, then click Enter to save your note.
1:30Press on any video thumbnail to jump immediately to the timecode shown.
Notes are saved with you account but can also be exported as plain text, MS Word, PDF, Google Doc, or Evernote.