Home arrow SQL Server arrow Page 3 - Using Triggers In MS SQL Server
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 / 592
January 01, 2002
TABLE OF CONTENTS:
  1. · Using Triggers In MS SQL Server
  2. · What exactly is a trigger?
  3. · UPDATE and DELETE triggers
  4. · Conclusion

print this article
SEARCH DEVARTICLES

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

- 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 
Support 

Developer Shed Affiliates

 




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