Quick Start Guide to SQL Server 7 -- Part 4, Page 3
Triggers give the developer more control over their data.
Not comparable with anything in Microsoft Access, triggers allow the developer to really control what information is allowed into the database, as well as what happens with it.
Triggers help maintain data integrity.
They fire off after certain events, such as the addition, update or deletion of a record.
Let's look at a few possible trigger uses:
- Enforce business rules if you have a few complex business rules that you can't handle via regular relationships and table constraints, you can throw them into triggers. For example, you might want to check every order to ensure the customer hasn't ordered more than three special offer video recorders
- Log transactions Perhaps you want to keep a log of what is happening within the database. Every time a record is altered in any way, you can write code to throw a log entry into a separate table (perhaps even a separate database)
- Maintain data integrity Relationships are really only triggers under another name. Every time you add or change records, a hidden trigger fires off and checks for data integrity. You can add your own using triggers, or perhaps implement Access features not supported by SQL Server relationships, such as Cascade Update and Cascade Delete
We'll attempt to cover the basics of implementing trigger statements in this section, though it's a complex topic and can be difficult to grasp. Don't worry if you don't understand it all at first.
To manage Triggers:
- View your Tables (as before)
- Right-click your Table
- Select 'All Tasks', 'Manage Triggers'
Let's now take a peek at a few sample triggers.
This trigger checks if a certain field in Table1 contains a particular value. If it does, that record is rejected:
CREATE TRIGGER MyTriggerName ON [TABLE1]-- This says create a trigger called MyTriggerName to monitor Table1 --FOR INSERT, UPDATE-- And launch this trigger every time an insert or update ---- is made on this table. The three default options are ---- INSERT, UPDATE, DELETE - add or remove as appropriate --ASDeclare @MyVariable as Char(10)-- This declares a variable called MyVariable as Char(10), ---- a string data type to hold a default ten characters --Select @ MyVariable = Inserted.MyFieldName from Inserted-- When inserting/update information, SQL Server creates a temporary ---- table (Inserted) to hold the information. When deleting, it uses ---- a table called Deleted. This bit of code uses an SQL statement ---- to retrieve information from the Inserted table and place ---- it into the MyVariable variable --If @MyVariable = "Karl"-- If MyVariable equals Karl then -- Begin Raiserror('Invalid entry. Choose another name', 16, 1) -- Raise an error -- Rollback Transaction -- Discard the record -- End -- Note that the Begin and End statements are just the -- -- boundaries of what should happen after the If statement --
Here's another example trigger:
CREATE TRIGGER SalaryLog ON [MyEmployees]FOR UPDATE-- Launch on an update --ASDECLARE @EmpName as VarChar(100), @EmpSalary as Money-- Declare two different variables of different data types --SELECT @EmpName = Inserted.EmployeeName,@EmpSalary = Inserted.SalaryFROM Inserted-- Grab the stuff being updated and place it into the variables -- INSERT INTO MyLog(Username, TheDate, Alteration)Values (USER_NAME() , GETDATE(), @EmpName + ' is now on ' + CONVERT(VarChar(10), @EmpSalary))-- Insert the logged on database user and the date into the log table ---- Also, insert a description - @EmpName added to ' is now on ' added ---- to @EmpSalary, which has been converted from the Money data type ---- to a varchar using the CONVERT function --
It's worth noting that triggers only fire off once even if you're altering multiple records. For example, you may be performing a mass update or delete and if just one of those records violates the rules of your trigger and you issue a ROLLBACK TRANSACTION, it'll stop the whole lot.
Also, triggers can't be called manually from within Visual Basic. They're simply 'triggered' by SQL Server.
For more information on triggers, look up the topic in the Books Online reference that ships with SQL Server 7.