Now Sharon feels ready to try a trigger. She wants to see if she can enforce the rule that no tutor should work more than 60 hours in a month. Sharon has done one or two trig-gers before, but she is very uncertain about where to start from. She decides to look up triggers in SQL Server’s Help files.
Things You Should Know
Triggers
Triggers are scripts of SQL code that are triggered by an event. The most common events are on INSERT, UPDATE, or DELETE. These triggers are specific to a given table. A trigger for INSERT into the student table, for instance, will fire every time that an INSERT into that table occurs. Triggers can respond to more than one event at a time. You could, for instance, have a trigger that responds to both the UPDATE and DELETE events.
Caution
If you use an UPDATE or a D E L E T E without a W H E R E clause, or if the WHERE clause isn’t specific enough, you can change or DELETE all the rows in a table. For
would set every session in the entire table to have the StudentKey
“snorton.” Worse, there is no easy undo. Once an UPDATE is committed, the only way to undo it would be to restore the tables from backup files and the logs. This is a tricky task and usually requires the database be offline while the files are restored. The same danger holds for the D E L E T E command.
DELETE FROM Session
This DELETE statement without a WHERE clause will delete every row in the Session table. Sometimes referential integrity can save you from this mistake, but in a table like Session that is on the child side of all of its relationships, the command will empty the table.
Triggers are used to enforce business rules that can’t be enforced by normal database constraints. In the Tutor database, there is a rule that no tutor can work more than 60 hours in a month. This is impossible to enforce just by referential integrity and constraints. But it can be enforced by a trigger. The rule that a student must be enrolled in a class to sign up for tutoring in that subject would be another candidate for a trigger.
SQL Server supports three kinds of triggers on tables. FOR and AFTER triggers let the INSERT, UPDATE, or DELETE occur and then run their SQL. INSTEAD OF triggers intercept the event and execute their code instead of the INSERT, UPDATE, or DELETE.
The basic syntax for a trigger is
CREATE TRIGGER <trigger_name> ON <table_name>
[FOR, AFTER, INSTEAD OF] [INSERT, UPDATE, DELETE]
AS
{SQL Code}
Sharon decides to use an INSTEAD OF trigger on
INSERT. She is not going to let the tutor enter a session if it brings the total hours to more than 60. Sharon knows it is important to list out the logical steps before trying to actually write the trigger. It is easy to get confused if you don’t have a clear recipe to follow. She lists these steps:
1.
Get the date from the INSERTED table.
2.
Extract the month.
3.
Create a variable for the total hours.
4.
Assign to total the sum of each session for that month (assuming 30 minutes each).
5.
Check to see if the sum >60.
6.
Check if it is output a message.
7.
Otherwise complete the insert into the Session table.
Things You Should Know
INSERTED and DELETED Tables
Whenever you insert a record, SQL Server creates an INSERTED table in the Temp database. The table only exists for the duration of the transaction, but within a trigger, you can use this table to access the data that was inserted. Updates and deletes are stored in a DELETED table.
First, Sharon defined the trigger and the internal variables she was going to use.
CREATE TRIGGER tr_SessionHours ON [Session]
INSTEAD OF INSERT AS
DECLARE @month INT DECLARE @Year INT
DECLARE @tutorID NCHAR(10) DECLARE @total FLOAT DECLARE @Maximum INT
The DECLARE keyword is used to declare internal variables. All variables in SQL Server must start with the “@” symbol. Next, she uses the SET keyword to assign a value to the @Maximum variable. The sessions are in minutes, so she multiplies 60 hours by 60 minutes per hour to get 3600.
SET @Maximum = 3600
Next, she uses SELECT statement to assign values from the INSERTED TABLE to the variables
@month and @tutorID.
SELECT @month = month(SessionDateKey) FROM Inserted SELECT @Year = Year(SessionDateKey) FROM Inserted SELECT @tutorID = TutorKey FROM Inserted
Now that she has these values, Sharon writes the equation to test the number of total hours.
She counts the sessions and multiplies by 30 minutes, then she adds 30 for the session being
inserted. In the WHERE clause, she makes sure that the count is only for the month, year, and tutor in question.
SELECT @Total = (Count(*) * 30) + 30 FROM Session WHERE TutorKey = @tutorID
AND Month(SessionDateKey) = @Month AND Year(SessionDateKey) = @Year
Finally, she tests the @total to see if it is less than @Maximum. If it is not, she performs the in-sert that the trigger aborted. She uses a SELECT to fill in the values for the INSERT.
IF @total <= @Maximum BEGIN
INSERT INTO Session(SessionDateKey, SessionTimeKey, TutorKey, CourseKey) (SELECT SessionDateKey, SessionTimeKey, TutorKey, CourseKey FROM Inserted) END
ELSE BEGIN
Print ¶Too many hours for this month·
END
Here is the whole trigger:
CREATE TRIGGER tr_SessionHours ON [Session]
INSTEAD OF INSERT AS
DECLARE @month INT DECLARE @Year INT
DECLARE @tutorID NCHAR(10) DECLARE @total FLOAT DECLARE @Maximum INT SET @Maximum = 3600
SELECT @month = month(SessionDateKey) FROM Inserted SELECT @Year = Year(SessionDateKey) FROM Inserted SELECT @tutorID = TutorKey FROM Inserted
SELECT @Total = (Count(*) * 30) + 30 FROM Session WHERE TutorKey = @tutorID
AND Month(SessionDateKey) = @Month AND Year(SessionDateKey) = @Year IF @total <= @Maximum
BEGIN
INSERT INTO Session(SessionDateKey, SessionTimeKey, TutorKey, CourseKey) (SELECT SessionDateKey, SessionTimeKey, TutorKey, CourseKey FROM Inserted) END
ELSE BEGIN
Print ¶Too many hours for this month·
END
To test this, Sharon must insert enough session data to get one of the tutors up to 3,600 min-utes, then add one more session. She does this and sees the message in the Query window.