Week 9 - Programmable SQL Continnued

Table Of Contents

• Triggers

---

Welcome to Week 9

Welcome to week 9 of 710, Advanced Database Systems. This week covers additional programmable objects and gives more possibilities on how to architect the database in order to provide services and tools for application developers.

Triggers

Triggers are a special type of stored procedure that are executed automatically when a specific event occurs within a database. Triggers are used to maintain the integrity of the database by executing a set of SQL statements when a specific event occurs. Triggers are used to enforce business rules, enforce referential integrity, and to maintain data consistency. Triggers are executed automatically when a specific event occurs within a database. Triggers are used to maintain the integrity of the database by executing a set of SQL statements when a specific event occurs. Triggers are used to enforce business rules, enforce referential integrity, and to maintain data consistency.

The syntax of a CREATE TRIGGER statement is:

CREATE OR ALTER TRIGGER trigger_name 
    ON {table_name|view_name}
    [WITH [ENCRYPTION] [,] [EXECUTE_AS_clause]] 
    {FOR|AFTER|INSTEAD OF} [INSERT] [,] [UPDATE] [,] [DELETE] 
    AS 
        sql_statements

Examples of Triggers

A CREATE TRIGGER statement that corrects mixed-case state names

CREATE OR ALTER TRIGGER trgVendors_INSERT_UPDATE ON Vendors
    AFTER INSERT,UPDATE 
AS 
    UPDATE Vendors
    SET VendorState = UPPER(VendorState) 
    WHERE VendorID IN (SELECT VendorID FROM Inserted);

An AFTER trigger that archives deleted data

CREATE OR ALTER TRIGGER trgInvoices_DELETE 
    ON Invoices 
    AFTER DELETE
AS
INSERT INTO InvoiceArchive
    (InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, 
    PaymentTotal, CreditTotal, TermsID, InvoiceDueDate, PaymentDate) 
    SELECT InvoiceID, VendorID, InvoiceNumber, InvoiceDate, InvoiceTotal, PaymentTotal, 
    CreditTotal, TermsID, InvoiceDueDate, PaymentDate
    FROM Deleted

A DELETE statement that causes the AFTER trigger to fire

DELETE Invoices 
WHERE VendorID = 37