Using Triggers In MS SQL Server - UPDATE and DELETE triggers

(Page 3 of 4 )

Now that we understand how an "INSERT" trigger works, let's take a look at "UPDATE" and "DELETE" triggers. Here's an "UPDATE" trigger:

CREATE TRIGGER trig_updateAuthor

ON authors

FOR UPDATE

AS

DECLARE @oldName VARCHAR(100)

DECLARE @newName VARCHAR(100)

IF NOT UPDATE(au_fName) AND NOT UPDATE(au_lName)

BEGIN

RETURN

END

SELECT @oldName = (SELECT au_fName + ' ' + au_lName FROM Deleted)

SELECT @newName = (SELECT au_fName + ' ' + au_lName FROM Inserted)

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:

UPDATE authors

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

ON authors

FOR DELETE

AS

DECLARE @isOnContract BIT

SELECT @isOnContract = (SELECT contract FROM Deleted)

IF(@isOnContract = 1)

BEGIN

PRINT "Code to notify publisher goes here"

END

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.

Next: Conclusion >>
 

More SQL Server Articles
More By David Rusik

Comments On: Using Triggers In MS SQL Server

· >> When this is the case, the "UPDATE" trigger is called for each row that was>>...    · Its really very helpfull for the beginners to learn it    · >> When this is the case, the "UPDATE" trigger is called for each row that was>>...    · >> When this is the case, the "UPDATE" trigger is called for each row that was >>...    · i have faced the same problem where i get an error which says multiple rows selected...    · its very helpful    · Very helpful for new ppl learning sql serverThanks,    · A)once triggers are called after specific times of insert command executions of...   >>> Post your comment now!