Views, Stored Procedures, Functies & Triggers

1. Views

Een view is gewoon een opgeslagen SELECT-query die je als virtuele tabel kan aanspreken — handig om complexe queries te hergebruiken of toegang te beperken.

Aanmaken

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

Gebruiken

SELECT * FROM dbo.PersoneelKantoor;

Opties

Optie	Effect
WITH SCHEMABINDING	Onderliggende tabellen mogen niet van structuur wijzigen zolang de view bestaat
WITH CHECK OPTION	INSERT/UPDATE via de view mislukt als het resulterende record niet aan de WHERE-conditie voldoet

WITH CHECK OPTION — voorbeeld

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

Een INSERT van een record met productLine = 'Classic Cars' via deze view geeft fout:

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

Rechten op een view

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

Verwijderen

DROP VIEW IF EXISTS dbo.PersoneelKantoor;

---

2. Stored Procedures

Een stored procedure = opgeslagen reeks T-SQL-instructies met parameters. Schrijf eenmalig, roep overal aan.

Aanmaken

-- Controleer eerst of de procedure al bestaat
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

Uitvoeren

EXEC dbo.GetCustomersFromCountry @country = 'Belgium';

Met conditiecontrole

CREATE PROCEDURE dbo.SetCreditLimitForCustomer
    @customerNumber INT,
    @newLimit       DECIMAL(10,2)
AS
BEGIN
    -- Alleen verhogen, nooit verlagen
    IF @newLimit > (SELECT creditLimit FROM customers
                    WHERE customerNumber = @customerNumber)
    BEGIN
        UPDATE customers
        SET    creditLimit = @newLimit
        WHERE  customerNumber = @customerNumber;
    END;
END;
GO

---

3. Functies

Een scalar function geeft één waarde terug en is — in tegenstelling tot een procedure — bruikbaar midden in een query.

Aanmaken

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

Gebruiken in een query

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

-- In een stored procedure als conditie
IF @newLimit > dbo.GetCreditLimitCustomer(@customerNumber)

Verschil procedure vs functie

Een procedure voert acties uit (INSERT, UPDATE, DELETE) en geeft geen waarde terug.
Een functie berekent en geeft een waarde terug — bruikbaar in SELECT, WHERE en andere expressies.

---

4. Triggers

Een trigger wordt automatisch uitgevoerd bij een INSERT, UPDATE of DELETE op een tabel.

Aanmaken — AFTER INSERT

CREATE TRIGGER dbo.Payments_CheckAmount
ON   dbo.Payments
AFTER INSERT
AS
BEGIN
    -- Negatief bedrag automatisch positief maken
    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 werd uitgevoerd: negatief bedrag gecorrigeerd.';
    END;
END;
GO

Virtuele tabellen in triggers

Tabel	Bevat
inserted	De nieuwe rijen (bij INSERT en UPDATE)
deleted	De oude rijen (bij DELETE en UPDATE)

Verwijderen

DROP TRIGGER IF EXISTS dbo.Payments_CheckAmount;