Practice creating SQL-Server Triggers

Lesson 6	Creating triggers, part 2
Objective	Practice creating triggers.

Practice creating SQL-Server Triggers

Suppose that your company has a business rule that no employee is allowed to have a salary greater than $200,000 unless the authorizing manager’s employee ID has been entered with the transaction. Assume that a table, named employees, contains these columns:

Column name	Data type
EmployeeID	`int`
Salary	`smallmoney`
HireDate	`smalldatetime`
ApprovalID	`int`

Create an INSERT Trigger

To enforce this business rule, you will create an INSERT trigger named trgSalary.

Triggers are special types of Stored Procedures that are defined to execute automatically in place of or after data modifications.
They can be executed automatically on the INSERT, DELETE and UPDATE triggering actions.
There are two different types of triggers in Microsoft SQL Server 2000. They are INSTEAD OF triggers and AFTER triggers.
These triggers differ from each other in terms of their purpose and when they are fired. In this article we shall discuss each type of trigger.

Creating an UPDATE trigger

You can create an UPDATE trigger that fires only when the Salary column is updated, like this:

CREATE TRIGGER trgSalary ON employees
FOR UPDATE
AS
IF UPDATE(Salary) 
  
IF (SELECT COUNT(*) FROM INSERTED WHERE 
Salary > 150000 AND ApprovalID IS NULL) > 0
   BEGIN
   --THE DATA INSERTED VIOLATES BUSINESS RULES
   --REMOVE ROWS FROM THE EMPLOYEES TABLE
   DELETE FROM employees
   FROM employees e JOIN INSERTED i 
     
ON (e.EmployeeID = i.EmployeeID)
 
WHERE e.Salary > 200000
  AND e.ApprovalID is NULL
  --RETURN AN ERROR BACK TO THE CALLING PROGRAM
   RAISERROR ('You must enter an ApprovalID for 
  salaries greater than $200,000', 16, 1)
   END

In the next lesson, modification of a trigger will be discussed.

Creating SQL Server Triggers - Exercise

Click the Exercise link below to practice creating triggers.
Creating SQL Server Triggers - Exercise