| 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 managers
employee ID has been entered with the transaction. Assume that a table, named employees, contains these columns:
| Column name || Data type |
| EmployeeID || |
| Salary || |
| HireDate || |
| ApprovalID || |
Create an INSERT Trigger
To enforce this business rule, you will create an
INSERT trigger named
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
IF (SELECT COUNT(*) FROM INSERTED WHERE
Salary > 150000 AND ApprovalID IS NULL) > 0
--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)
In the next lesson, modification of a trigger will be discussed.
Creating SQL Server Triggers - Exercise