For instance, suppose that room numbers for
different dormitories have different domains. That is, the room numbers for one dorm use two digits, for another
dorm use three, and for another use four. Validating all room numbers automatically would be impossible
with standard CHECK constraints, because CHECK constraints do not support such complex logic. However, a
trigger could be written to provide for any level of complexity.
Unlike stored procedures that are executed when called by a user or a program, triggers are executed when
an INSERT, UPDATE, or DELETE statement makes a change to a table. Triggers can be written to fire
BEFORE, AFTER, or INSTEAD OF the INSERT, UPDATE, or DELETE.
Here is an example AFTER trigger written in Microsoft Transact-SQL. Whenever a row is inserted in the
Student table, and whenever a row in the Student table is updated, then this code executes after the change
has been made to the Student table. The data change triggers the code. Again, we provide this code as a realistic
example only, and we will not explain the syntax in any detail.
CREATE TRIGGER RoomCheck ON Student
FOR INSERT, UPDATE
AS
declare @Dorm varchar(20)
declare @Room int
IF UPDATE (Room)
CHAP. 8] DATABASE 159
Select @Dorm = Dorm from inserted
Select @Room = Room from inserted
IF @Dorm = 'Williams' and (@Room > 999 or @Room < 100)
BEGIN
PRINT 'Williams dorm has 3 digit room numbers.
Pages:
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437