数据库系统第12节触发器

最新推荐文章于 2024-10-16 11:14:53 发布

hummhumm

最新推荐文章于 2024-10-16 11:14:53 发布

阅读量1.1k

点赞数 16

文章标签：数据库 oracle java python mysql sql database

本文链接：https://blog.csdn.net/hummhumm/article/details/141128614

版权

触发器（Trigger）是一种在关系型数据库管理系统中使用的特殊存储过程。当特定的事件（如数据的插入、更新或删除）发生在指定的表上时，触发器会自动执行预定义的动作。触发器可以用来维护数据的一致性、执行复杂的业务规则或者记录审计信息等。

触发器的基本组成部分：

触发事件：导致触发器执行的操作，通常是INSERT、UPDATE或DELETE语句。
触发时间：触发器何时被激活，通常是在操作之前（BEFORE）还是之后（AFTER）。
触发动作：触发器执行的具体SQL代码块。

触发器的类型：

DML触发器（Data Manipulation Language Triggers）:
- BEFORE INSERT: 在插入新行之前执行。
- AFTER INSERT: 在插入新行之后执行。
- BEFORE UPDATE: 在更新现有行之前执行。
- AFTER UPDATE: 在更新现有行之后执行。
- BEFORE DELETE: 在删除现有行之前执行。
- AFTER DELETE: 在删除现有行之后执行。
DDL触发器（Data Definition Language Triggers）:
- 在执行诸如CREATE, ALTER, DROP等语句时触发。
登录触发器:
- 当用户登录到数据库服务器时触发。

触发器的工作原理：

创建触发器：使用CREATE TRIGGER语句定义触发器。
触发条件：当满足触发条件时，触发器被激活。
执行触发器：根据定义的动作执行相应的SQL语句。

触发器的使用示例：

假设有一个员工表Employees，我们需要在每次更新员工薪水时记录一条日志。

创建触发器：

CREATE TRIGGER trg_after_update_salary
AFTER UPDATE ON Employees
FOR EACH ROW
BEGIN
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO SalaryLog (employee_id, old_salary, new_salary)
        VALUES (OLD.id, OLD.salary, NEW.salary);
    END IF;
END;

注意事项：

触发器可能会增加系统的复杂性和开销。
如果设计不当，可能引起死锁或循环触发。
需要仔细考虑性能影响，特别是对于大型数据库系统。

总结：

触发器是一个强大的工具，用于确保数据完整性、执行复杂的业务逻辑和审计跟踪等功能。然而，它们也需要谨慎使用以避免不必要的性能问题。

我们可以通过几个具体的案例来进一步了解触发器的应用场景和实现细节。以下是基于一个简单的图书管理系统的一些示例。

案例背景：

假设我们有一个图书管理数据库，其中包含以下表结构：

Books (book_id, title, author, price, quantity)
Authors (author_id, name, email)
SalesLog (log_id, book_id, sale_date, quantity, total_price)

我们的目标是：

当作者的名字发生变化时，需要记录这次变化。
当书籍售出时，需要减少库存，并记录销售日志。
当书籍的库存低于某个阈值时，自动发送邮件提醒需要补货。

案例 1: 记录作者名称的变化

目标：

每当作者的名字发生更改时，将旧名字和新名字记录在一个日志表中。

步骤：

创建日志表:

CREATE TABLE AuthorNameLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    author_id INT,
    old_name VARCHAR(100),
    new_name VARCHAR(100),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器:

DELIMITER //
CREATE TRIGGER update_author_name_log
AFTER UPDATE ON Authors
FOR EACH ROW
BEGIN
    IF OLD.name != NEW.name THEN
        INSERT INTO AuthorNameLog (author_id, old_name, new_name)
        VALUES (OLD.author_id, OLD.name, NEW.name);
    END IF;
END; //
DELIMITER ;

案例 2: 销售日志与库存更新

目标：

每当书籍售出时，更新库存数量并记录销售信息。

步骤：

创建销售日志表:

CREATE TABLE SalesLog (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    sale_date DATE,
    quantity INT,
    total_price DECIMAL(10, 2)
);

创建触发器:

DELIMITER //
CREATE TRIGGER update_stock_and_sales_log
AFTER INSERT ON SalesLog
FOR EACH ROW
BEGIN
    DECLARE new_quantity INT;
    SELECT quantity INTO new_quantity FROM Books WHERE book_id = NEW.book_id;
    
    IF new_quantity IS NOT NULL THEN
        SET new_quantity = new_quantity - NEW.quantity;
        UPDATE Books SET quantity = new_quantity WHERE book_id = NEW.book_id;
    END IF;
END; //
DELIMITER ;

案例 3: 库存预警

目标：

当书籍的库存数量低于5时，发送一封电子邮件给管理员。

步骤：

创建触发器:

DELIMITER //
CREATE TRIGGER send_inventory_alert
AFTER UPDATE ON Books
FOR EACH ROW
BEGIN
    DECLARE inventory_level INT;
    SELECT quantity INTO inventory_level FROM Books WHERE book_id = OLD.book_id;
    
    IF inventory_level <= 5 THEN
        -- 发送邮件的逻辑（这里只是一个示例）
        CALL send_email('inventory@library.com', 'Low Inventory Alert', CONCAT('Book "', OLD.title, '" is running low with only ', inventory_level, ' copies left.'));
    END IF;
END; //
DELIMITER ;

注意事项：

在实际应用中，发送邮件的逻辑通常不会直接写入触发器中，而是通过调用存储过程或其他方式间接实现。
触发器中的逻辑应当尽可能简单，避免复杂的业务逻辑导致性能问题。

这些案例展示了如何使用触发器来自动处理一些常见的数据库任务，从而提高应用程序的可靠性和效率。

接下来我们将通过更多的案例来深入探讨触发器的应用。假设我们有一个更复杂的图书管理系统，其中包括以下表结构：

Books (book_id, title, author, publisher, publication_date, price, quantity)
Authors (author_id, name, email)
Publishers (publisher_id, name, address)
SalesLog (log_id, book_id, sale_date, quantity, total_price)
AuthorNameLog (log_id, author_id, old_name, new_name, change_date)
InventoryAlerts (alert_id, book_id, alert_date, quantity)

我们的目标是：

当书籍的价格发生变化时，记录价格变动的历史。
当书籍售出后，如果库存数量低于某个阈值，则自动发送库存预警邮件。
当书籍的出版日期更新时，检查书籍是否属于新的出版物，并将其添加到“新书”列表中。

案例 4: 记录书籍价格的变化

目标：

每当书籍的价格发生更改时，记录价格变动历史。

步骤：

创建价格变动历史表:

CREATE TABLE PriceHistory (
    history_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    old_price DECIMAL(10, 2),
    new_price DECIMAL(10, 2),
    change_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

创建触发器:

DELIMITER //
CREATE TRIGGER update_price_history
AFTER UPDATE ON Books
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO PriceHistory (book_id, old_price, new_price)
        VALUES (OLD.book_id, OLD.price, NEW.price);
    END IF;
END; //
DELIMITER ;

案例 5: 库存预警邮件

目标：

当书籍售出后，如果库存数量低于10本，则自动发送库存预警邮件给管理员。

步骤：

创建库存预警表:

CREATE TABLE InventoryAlerts (
    alert_id INT AUTO_INCREMENT PRIMARY KEY,
    book_id INT,
    alert_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    quantity INT
);

创建触发器:

DELIMITER //
CREATE TRIGGER check_inventory_level
AFTER INSERT ON SalesLog
FOR EACH ROW
BEGIN
    DECLARE current_quantity INT;
    SELECT quantity INTO current_quantity FROM Books WHERE book_id = NEW.book_id;

    IF current_quantity < 10 THEN
        INSERT INTO InventoryAlerts (book_id, quantity) VALUES (NEW.book_id, current_quantity);
        -- 假设发送邮件的逻辑已经实现
        CALL send_email('inventory@library.com', 'Inventory Alert', CONCAT('The inventory of book "', (SELECT title FROM Books WHERE book_id = NEW.book_id), '" has fallen below 10.'));
    END IF;
END; //
DELIMITER ;

案例 6: 新书列表

目标：

当书籍的出版日期更新为最近一年内时，将其加入“新书”列表中。

步骤：

创建新书列表表:

CREATE TABLE NewBooks (
    book_id INT PRIMARY KEY,
    publication_date DATE,
    FOREIGN KEY (book_id) REFERENCES Books(book_id)
);

创建触发器:

DELIMITER //
CREATE TRIGGER add_to_new_books_list
AFTER UPDATE ON Books
FOR EACH ROW
BEGIN
    DECLARE current_year INT;
    SET current_year = YEAR(CURRENT_DATE);

    IF OLD.publication_date != NEW.publication_date AND YEAR(NEW.publication_date) >= current_year - 1 THEN
        INSERT INTO NewBooks (book_id, publication_date) VALUES (NEW.book_id, NEW.publication_date);
    END IF;
END; //
DELIMITER ;