Integrity Constraints & Triggers

Integrity enhancement features

SQL lets us define five types of integrity constraint:

1. Required data

Some columns must always contain a valid value — e.g. every member of staff must have a job position (Manager, Assistant, …).

ALTER TABLE Staff
ALTER COLUMN position VARCHAR(50) NOT NULL;

2. Domain constraints

Every column has a domain (set of legal values). E.g. gender is a single character, either ‘M’ or ‘F’.

ALTER TABLE Staff WITH NOCHECK
ADD CONSTRAINT CK1 CHECK (gender = 'M' OR gender = 'F');

3. Entity integrity

The primary key of a table must contain a unique, non-null value for each row.

PRIMARY KEY (staffNo)
PRIMARY KEY (clientNo, propertyNo)     -- composite key
UNIQUE (telNo)                         -- alternate key

You can only have one PRIMARY KEY clause per table; uniqueness of alternate keys is ensured with UNIQUE.

4. Referential integrity

A foreign key (FK) is a column (or set of columns) that links each row in the child table to the row of the parent table containing the matching primary key.

FOREIGN KEY (branchNo) REFERENCES Branch (branchNo)

• Any INSERT/UPDATE that tries to create an FK value in the child with no matching parent key is rejected.
• What happens when you update/delete a parent key that has matching child rows depends on the referential action in the ON UPDATE / ON DELETE sub-clauses:

FOREIGN KEY (staffNo) REFERENCES Staff ON DELETE SET NULL
FOREIGN KEY (ownerNo) REFERENCES PrivateOwner ON UPDATE CASCADE

Reading: if a staff member is deleted, their properties become unassigned (staffNo = NULL). If an owner’s number changes, it is automatically changed in all their properties.

5. Enterprise constraints (“business rules”)

Additional rules specified by users or the organization. They can be applied using triggers. DreamHome examples:

• A member of staff may not manage more than 100 properties at the same time.
• If the job position is Manager, the salary must not exceed 30,000.

What are triggers?

• Triggers are a powerful tool to enforce domain, entity, referential integrity and enterprise constraints (business rules).
• A trigger is a special kind of stored procedure.
• Three categories: DML triggers, DDL triggers and logon triggers.
• DML triggers cannot be called directly. They are attached to individual tables or views and fire in response to INSERT, UPDATE or DELETE.

AFTER vs INSTEAD OF triggers

The inserted and deleted tables

Triggers use two special tables that are only accessible inside triggers. Their data is available immediately after the INSERT, UPDATE or DELETE.

When an UPDATE executes, the database engine effectively deletes the existing row (holding it in deleted) and adds a new row (holding it in inserted). The new row has the same data except for the modified columns.

When are DML triggers used?

• Make changes to other columns (same or other tables) in response to a modification.
• Perform auditing — record who made a change and when.
• Roll back or undo data modifications.
• Provide specialized error messages.

Some facts about triggers

• CHECK constraints can reference only the columns of their own table. Cross-table constraints (business rules) must be defined as triggers.
• Triggers can enforce complex business logic that is difficult or impossible with other integrity mechanisms.
• Triggers can cascade changes through related tables.
• Triggers can evaluate the state of a table before and after a modification and act on the difference.

Syntax for creating triggers

CREATE TRIGGER [ schema_name. ] trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
{ sql_statement [ ; ] [ ...n ] }

How an INSERT trigger works

1. INSERT statement executed
2. INSERT statement logged
3. AFTER INSERT trigger statements executed

• When an INSERT trigger fires, new rows are added to both the trigger table and the inserted table.
• inserted is a temporary table holding a copy of the inserted rows, letting you reference logged data from the initiating INSERT.
• The trigger can examine inserted to decide whether/how to act.

CREATE TRIGGER [insrtWorkOrder] ON [Production].[WorkOrder]
AFTER INSERT AS
BEGIN
    SET NOCOUNT ON;
    INSERT INTO [Production].[TransactionHistory]
        ([ProductID], [ReferenceOrderID], [TransactionType],
         [TransactionDate], [Quantity], [ActualCost])
    SELECT inserted.[ProductID], inserted.[WorkOrderID],
           'W', GETDATE(), inserted.[OrderQty], 0
    FROM inserted;
END

Every new work order automatically writes a matching row to the transaction history.

Example: rejecting an insert (business rule)

CREATE TRIGGER LowCredit
ON Purchasing.PurchaseOrderHeader
AFTER INSERT
AS
    DECLARE @creditrating int

    SELECT @creditrating = v.CreditRating
    FROM   Purchasing.PurchaseOrderHeader p
           INNER JOIN inserted i ON p.PurchaseOrderID = i.PurchaseOrderID
           JOIN Purchasing.Vendor v ON v.VendorID = i.VendorID

    IF @creditrating = 5
    BEGIN
        RAISERROR ('This vendor''s credit rating is too low to accept new purchase orders.', 16, 1)
        ROLLBACK TRANSACTION
    END

Checks the vendor’s credit rating whenever a new purchase order is inserted; if it is poor (5), raises an error and rolls back the insert.

How a DELETE trigger works

1. DELETE statement executed
2. DELETE statement logged
3. AFTER DELETE trigger statements executed

• Deleted rows are placed in the special deleted table — a logical table holding a copy of the removed rows.
• When a row is appended to deleted, it no longer exists in the database table, so deleted and the table have no rows in common.
• Space for deleted is allocated from memory; it is always in the cache.

CREATE TRIGGER [delCategory] ON [Categories]
AFTER DELETE AS
BEGIN
    UPDATE P SET [Discontinued] = 1
    FROM [Products] P
         INNER JOIN deleted AS d ON P.[CategoryID] = d.[CategoryID]
END;

When a category is deleted, all its products are marked as discontinued.

How an UPDATE trigger works

1. UPDATE statement executed
2. UPDATE statement logged
3. AFTER UPDATE trigger statements executed

• Original rows (before image) move into deleted; updated rows (after image) go into inserted.
• The trigger can examine both tables (and the updated table) to see how many rows changed and how to act.
• Use IF UPDATE(column) to react only when a specific column is updated.

CREATE TRIGGER [updtProductReview] ON [Production].[ProductReview]
AFTER UPDATE NOT FOR REPLICATION AS
BEGIN
    UPDATE [Production].[ProductReview]
    SET    [Production].[ProductReview].[ModifiedDate] = GETDATE()
    FROM   inserted
    WHERE  inserted.[ProductReviewID] = [Production].[ProductReview].[ProductReviewID];
END;

Automatically stamps ModifiedDate whenever a review is updated. NOT FOR REPLICATION stops the trigger firing when a replication agent makes the change.

How an INSTEAD OF trigger works

1. UPDATE, INSERT or DELETE statement executed
2. Executed statement does not occur
3. INSTEAD OF trigger statements executed

• Executes instead of the original triggering action.
• Increases the variety of updates you can perform against a view.
• Each table or view is limited to one INSTEAD OF trigger per triggering action (INSERT, UPDATE or DELETE).
• Lets you code logic that rejects parts of a batch while allowing other parts to succeed.

CREATE TRIGGER [delEmployee] ON [HumanResources].[Employee]
INSTEAD OF DELETE NOT FOR REPLICATION AS
BEGIN
    SET NOCOUNT ON;
    DECLARE @DeleteCount int;
    SELECT @DeleteCount = COUNT(*) FROM deleted;
    IF @DeleteCount > 0
    BEGIN
        -- e.g. raise an error: employees cannot be deleted, only marked inactive
        ...
    END;
END;

Altering and dropping triggers

ALTER TRIGGER [ schema_name. ] trigger_name
ON { table | view }
{ FOR | AFTER | INSTEAD OF }
{ [ INSERT ] [ , ] [ UPDATE ] [ , ] [ DELETE ] }
AS
{ sql_statement [ ; ] [ ...n ] }

DROP TRIGGER [ schema_name. ] trigger_name

Worked example: DreamHome business rules

CREATE TRIGGER trgManagerSalary ON Staff
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT * FROM inserted
               WHERE position = 'Manager' AND salary > 30000)
    BEGIN
        RAISERROR('A manager''s salary must not exceed 30000.', 16, 1)
        ROLLBACK TRANSACTION
    END
END

CREATE TRIGGER trgMaxProperties ON PropertyForRent
AFTER INSERT, UPDATE
AS
BEGIN
    IF EXISTS (SELECT p.staffNo
               FROM   PropertyForRent p
               WHERE  p.staffNo IN (SELECT staffNo FROM inserted)
               GROUP BY p.staffNo
               HAVING COUNT(*) > 100)
    BEGIN
        RAISERROR('Staff member already manages 100 properties.', 16, 1)
        ROLLBACK TRANSACTION
    END
END

Quick review