Home arrow SQL Server arrow Page 3 - Using Triggers In MS SQL Server

Using Triggers In MS SQL Server

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.

Author Info:
By: David Rusik
Rating: 4 stars4 stars4 stars4 stars4 stars / 593
January 01, 2002
  1. · Using Triggers In MS SQL Server
  2. · What exactly is a trigger?
  3. · UPDATE and DELETE triggers
  4. · Conclusion

print this article

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









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



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.
blog comments powered by Disqus

- Executing SQL Server Stored Procedure from P...
- How to Search for Date and Time Values Using...
- Replication: SQL Server 2000 - Part 2
- Replication: SQL Server 2000 - Part 1
- SQL Sever: Storing Code in Binary or Text Fi...
- Execute SQL on Multiple Tables/Columns - New...
- How to Connect to a SQL Server from Visual F...
- SQL Server Hardware Tuning and Performance M...
- Primary Key on Multiple Tables New RDBMS C...
- Migrating from Sybase to SQL Server
- What's Best for DBAs? GUI or T-SQL Comma...
- How to Perform a SQL Server Performance Audit
- An Introduction To The Bulk Copy Utility
- SQL Server Stored Procedures 101
- Building Your First SQL Server 2000 Database

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 

Developer Shed Affiliates


© 2003-2015 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials