在sql语句中用触发器实现在表中插入数据导致其他表数值-1
前言
在sql语句中用触发器实现在表中插入数据导致其他表数值-1并插入多条数据进行测试
一、使用步骤
1.创建两个数据表
CREATE TABLE books (
id INT PRIMARY KEY,
title VARCHAR(255),
author VARCHAR(255),
category VARCHAR(255),
price DECIMAL(10, 2),
publish_date DATE
);
CREATE TABLE library (
book_id INT PRIMARY KEY,
quantity INT
);
2.创建触发器
代码如下(示例):
CREATE TRIGGER decrement_quantity
ON books
AFTER INSERT
AS
BEGIN
-- 获取刚插入的图书的ID
DECLARE @book_id INT;
SELECT @book_id = id FROM inserted;
-- 更新library表中对应图书的数量减1
UPDATE library
SET quantity = quantity - 1
WHERE book_id = @book_id;
END;
3.插入数据进行测试
INSERT INTO library VALUES(1,23);
INSERT INTO library VALUES(2,10);
INSERT INTO books (id, title, author, category, price, publish_date)
VALUES (1, 'Book Title 1', 'Author 1', 'Category 1', 10.99, '2021-01-01');
INSERT INTO books (id, title, author, category, price, publish_date)
VALUES (2, 'Book Title 2', 'Author 1', 'Category 1', 15.99, '2021-01-01');