Triggers allow us to execute a batch of SQL code when either an insert, update or delete command is executed against a specific table. In this article David will describe exactly what triggers are, he will show you how to create new triggers from scratch, how to test triggers, and also provide you with some valuable links/books to help you learn more about triggers.
PRINT 'Name changed from "' + @oldName + '" to "' + @newName + '"'
This trigger would automatically be executed whenever we updated one/more records in the "authors" table. It starts out by creating two new variables: oldName and newName. The "UPDATE" function is used to check whether or not the "au_fName" and "au_lName" fields have been updated by the "UPDATE" query that executed the "trig_updateAuthor" trigger. If both fields haven't, then the trigger returns control to SQL server.
As I already mentioned, "UPDATE" triggers have access to two virtual tables: Deleted (which contains all of the fields and values for the records before they were updated), and Inserted (which contains all of the fields and values for the records after they have been updated). We get the value of the users name before the update from the "Deleted" table and store it in the "oldName" variable.
The updated name is stored in the "newName" variable, and is extracted from the virtual table, "Inserted". Lastly, both the authors name before and after the update query are printed.
So, if we ran an update query (through Query Analyzer) like this:
SET au_lName = 'Black'
WHERE au_id = '172-32-1176'
... then Query Analyzer would display the following text in the results pane:
Name changed from "John Doe" to "John Black"
Update triggers can also be used to check field constraints and relationships. The "contract" field of the "authors" table is a bit field representing whether or not this author has a contract with their publisher. The publisher may require notification of when an author who is on contract is removed from the "authors" table.
We could create a "DELETE" trigger on the "authors" table that would do this for us automatically:
CREATE TRIGGER trig_delAuthor
DECLARE @isOnContract BIT
SELECT @isOnContract = (SELECT contract FROM Deleted)
IF(@isOnContract = 1)
PRINT "Code to notify publisher goes here"
The "DELETE" trigger follows the same format and keyword syntax as the "INSERT" and "UPDATE" triggers. The only difference is that the "DELETE" trigger has access to the virtual table "Deleted", which contains all of the deleted rows from the "DELETE" command that triggered the "trig_delAuthor" trigger in the first place.