Views, Stored Procedures, Functions & Triggers

1. Views

A view is simply a stored SELECT query that you can address as a virtual table — handy for reusing complex queries or restricting access.

Creating

CREATE VIEW dbo.PersoneelKantoor
WITH SCHEMABINDING
AS
    SELECT e.firstName, e.lastName,
           o.addressLine1, o.addressLine2, o.city, o.postalCode
    FROM   dbo.employees e
    JOIN   dbo.offices   o ON e.officeCode = o.officeCode;
GO

Using It

SELECT * FROM dbo.PersoneelKantoor;

Options

Option	Effect
WITH SCHEMABINDING	The underlying tables may not change structure while the view exists
WITH CHECK OPTION	An INSERT/UPDATE via the view fails if the resulting record doesn't meet the WHERE condition

WITH CHECK OPTION — Example

CREATE VIEW dbo.Motors
AS
    SELECT productCode, productName, productLine, productScale,
           productVendor, productDescription, quantityInStock,
           buyPrice, MSRP
    FROM   products
    WHERE  productLine = 'Motorcycles'
    WITH CHECK OPTION;
GO

An INSERT of a record with productLine = 'Classic Cars' through this view produces an error:

The attempted insert or update failed because the target view specifies WITH CHECK OPTION

Permissions on a View

GRANT SELECT ON dbo.PersoneelKantoor TO [Fanny];
GO

Removing

DROP VIEW IF EXISTS dbo.PersoneelKantoor;

---

2. Stored Procedures

A stored procedure = a stored sequence of T-SQL statements with parameters. Write it once, call it anywhere.

Creating

-- First check if the procedure already exists
IF OBJECT_ID('dbo.GetCustomersFromCountry', 'P') IS NOT NULL
    DROP PROCEDURE dbo.GetCustomersFromCountry;
GO

CREATE PROCEDURE dbo.GetCustomersFromCountry
    @country NVARCHAR(100)
AS
BEGIN
    SELECT customerName
    FROM   customers
    WHERE  country = @country
    ORDER BY customerName;
END;
GO

Running It

EXEC dbo.GetCustomersFromCountry @country = 'Belgium';

With a Condition Check

CREATE PROCEDURE dbo.SetCreditLimitForCustomer
    @customerNumber INT,
    @newLimit       DECIMAL(10,2)
AS
BEGIN
    -- Only increase, never decrease
    IF @newLimit > (SELECT creditLimit FROM customers
                    WHERE customerNumber = @customerNumber)
    BEGIN
        UPDATE customers
        SET    creditLimit = @newLimit
        WHERE  customerNumber = @customerNumber;
    END;
END;
GO

---

3. Functions

A scalar function returns one value and — unlike a procedure — can be used in the middle of a query.

Creating

CREATE FUNCTION dbo.GetCreditLimitCustomer
    (@customerNumber INT)
RETURNS DECIMAL(10,2)
AS
BEGIN
    RETURN (
        SELECT creditLimit
        FROM   customers
        WHERE  customerNumber = @customerNumber
    );
END;
GO

Using It in a Query

-- Directly in a SELECT
SELECT customerNumber,
       customerName,
       dbo.GetCreditLimitCustomer(customerNumber) AS creditLimit
FROM   customers;

-- In a stored procedure as a condition
IF @newLimit > dbo.GetCreditLimitCustomer(@customerNumber)

Difference between a procedure and a function

A procedure performs actions (INSERT, UPDATE, DELETE) and doesn't return a value. A function computes and returns a value — usable in SELECT, WHERE and other expressions.

---

4. Triggers

A trigger is automatically executed on an INSERT, UPDATE or DELETE against a table.

Creating — AFTER INSERT

CREATE TRIGGER dbo.Payments_CheckAmount
ON   dbo.Payments
AFTER INSERT
AS
BEGIN
    -- Automatically make a negative amount positive
    IF EXISTS (SELECT 1 FROM inserted WHERE amount < 0)
    BEGIN
        UPDATE p
        SET    p.amount = ABS(p.amount)
        FROM   dbo.Payments p
        JOIN   inserted    i ON p.customerNumber = i.customerNumber
                            AND p.checkNumber    = i.checkNumber;

        PRINT 'Trigger Payments_CheckAmount ran: negative amount corrected.';
    END;
END;
GO

Virtual Tables in Triggers

Table	Contains
inserted	The new rows (on INSERT and UPDATE)
deleted	The old rows (on DELETE and UPDATE)

Removing

DROP TRIGGER IF EXISTS dbo.Payments_CheckAmount;