Learningsuch as何creation, using and managementSQLite触发器, implementationdatalibraryoperation automation
触发器 is datalibraryin 一种特殊object, 它会 in specific datalibraryoperation (such asINSERT, UPDATE, DELETE) 发生时自动执行. SQLite触发器可以用于:
SQLitesupport三种class型 触发器:
usingCREATE TRIGGER语句creation触发器, basic语法such as under :
CREATE [TEMP|TEMPORARY] TRIGGER [IF NOT EXISTS] trigger_name
[BEFORE|AFTER|INSTEAD OF] [INSERT|UPDATE|DELETE]
ON table_name
[FOR EACH ROW]
[WHEN condition]
BEGIN
-- 触发器operation
END;
-- creation一个记录usercreation时间 触发器
CREATE TRIGGER IF NOT EXISTS set_user_created_at
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = CURRENT_TIMESTAMP;
END IF;
END;
-- creation一个记录userupdate时间 触发器
CREATE TRIGGER IF NOT EXISTS set_user_updated_at
BEFORE UPDATE ON users
FOR EACH ROW
BEGIN
SET NEW.updated_at = CURRENT_TIMESTAMP;
END;
in 触发器in, SQLiteproviding了两个特殊 临时表:
NEW表package含了将要插入 or update data:
OLD表package含了update or delete before data:
-- creation一个audit触发器, 记录userdata 变更
CREATE TRIGGER IF NOT EXISTS audit_user_changes
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
INSERT INTO user_audit (
user_id,
old_name,
new_name,
old_email,
new_email,
changed_at
) VALUES (
OLD.id,
OLD.name,
NEW.name,
OLD.email,
NEW.email,
CURRENT_TIMESTAMP
);
END;
fake设我们 has 一个library存managementsystem, 需要 in 订单creation时自动reducinglibrary存:
-- creation产品表
CREATE TABLE IF NOT EXISTS products (
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
stock INTEGER NOT NULL DEFAULT 0
);
-- creation订单表
CREATE TABLE IF NOT EXISTS orders (
id INTEGER PRIMARY KEY,
product_id INTEGER NOT NULL,
quantity INTEGER NOT NULL,
order_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
FOREIGN KEY (product_id) REFERENCES products(id)
);
-- creation触发器, in 订单creation时reducinglibrary存
CREATE TRIGGER IF NOT EXISTS update_stock_after_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
UPDATE products
SET stock = stock - NEW.quantity
WHERE id = NEW.product_id;
END;
-- test触发器
INSERT INTO products (name, stock) VALUES ('iPhone 13', 100);
INSERT INTO orders (product_id, quantity) VALUES (1, 5);
-- 查看library存变化
SELECT * FROM products WHERE id = 1;
这个触发器会 in 每次creation订单时自动reducing for 应产品 library存, 确保library存data 准确性.
using以 under commands查看datalibraryin 所 has 触发器:
SELECT name, tbl_name, type, sql FROM sqlite_master WHERE type = 'trigger';
usingDROP TRIGGER语句delete触发器:
DROP TRIGGER IF EXISTS trigger_name;
SQLite不support直接modify触发器, 需要先delete然 after 重 new creation:
-- 先delete old 触发器
DROP TRIGGER IF EXISTS set_user_created_at;
-- 再creation new 触发器
CREATE TRIGGER IF NOT EXISTS set_user_created_at
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = CURRENT_TIMESTAMP;
END IF;
IF NEW.status IS NULL THEN
SET NEW.status = 'active';
END IF;
END;
1. 触发器可以 in 哪些operation时触发？ ( many 选)
2. in UPDATE触发器in, 哪个表package含update before data？
3. 实践练习: creation一个触发器, in deleteuser时, 同时delete该user 所 has 订单.
提示: fake设我们 has users表 and orders表, orders表 has 一个user_id字段关联 to users表 id字段.