当您创建一个数据库支持的应用程序时，您编写代码以确保相关字段中的数据完整性。
如果一件商品已售出，您将更新库存。如果有人取款，您会更新他们的余额。每当有人进行更改时，您都会重新计算总计并更新数据库中的相关字段。
通常这是在您的主代码中完成的：您的 JavaScript、Python、Ruby、Java 或其他代码。
但是，如果某些未来的代码不使用您当前至关重要的功能怎么办？新的后端接口、API 挂钩或简单的 shell 脚本可能不会使用您当前的代码。新代码将直接访问数据库，而不是使用旧代码。
我在上一家公司遇到过这种情况，并且感到库存甚至财务出现问题的痛苦，这一切都是因为一些新代码正在直接访问数据库。
所以这就是为什么我现在成为一名传道者，因为将您的关键代码放入数据库本身是多么重要。这是数据逻辑（不是“业务逻辑”），应该绑定到数据。可以触发数据库功能，确保完整性，无论外部代码访问它。
这是 PostgreSQL 中的一个示例，使用购物车：
首先，制作三个简单的表格：
- 有价格的物品。
- 带有数量的订单项。
- 带有总价的发票。
创建两个示例项目，一个 5 美元和一个 9 美元的项目。并创建发票#1 进行测试。
create table items ( id serial primary key, price int not null check (price > 0) ); create table invoices ( id serial primary key, total int ); create table lineitems ( invoice_id int not null references invoices(id), item_id int not null references items(id), quantity int not null check (quantity > 0), primary key (invoice_id, item_id) ); -- example data: insert into items (price) values (5); insert into items (price) values (9); insert into invoices (total) values (0);
现在，您希望它在更改 Lineitems 时重新计算发票总额。那是一个叫做触发器的特殊函数。首先创建函数，然后创建执行函数的触发器。
-- re-calculate the total of a lineitem's invoice create function recalc() returns trigger as $$ begin -- update invoice using lineitems's invoice_id update invoices set total = ( select sum(quantity * price) from lineitems join items on lineitems.item_id = items.id where invoice_id = new.invoice_id) where id = new.invoice_id; return new; end; $$ language plpgsql; -- run this function after any change to lineitems create trigger recalc after insert or update or delete on lineitems for each row execute procedure recalc();
通过添加 Lineitems 并更新它们的数量来测试它。每次更改后，查看发票。有用！
insert into lineitems (invoice_id, item_id, quantity) values (1, 1, 1); insert into lineitems (invoice_id, item_id, quantity) values (1, 2, 1); select * from lineitems; select * from invoices; select 'Notice new total when you update quantity:' look; update lineitems set quantity = 5 where invoice_id = 1 and item_id = 2; select * from lineitems; select * from invoices; select 'But when you delete a line? Oh no. Total is still $50' look; delete from lineitems where invoice_id = 1 and item_id = 2; select * from lineitems; select * from invoices; -- Let's try again in recalc-4.sql drop function recalc() cascade;
但请注意，如果您删除 Lineitem，它不会更新 Invoice！我勒个去？为什么不？
啊，那是因为该函数引用了一个名为“new”的记录变量，由 Lineitems 上的触发器传递，并使用它来获取 invoice_id。但是当你删除一个 Lineitem 时，记录变量被称为“old”，因为它引用了一个已经被删除的 Lineitem。该死。
您可以制作两个单独的触发器，一个使用“new”，只在更新和插入操作时调用，另一个使用“old”，只在删除时调用。但是你会重复总数的计算。因此，只需添加一个丑陋的 if/then 来将“new”或“old”分配给名为“r”的变量，并使用“r”代替。这是更新的功能：
-- re-calculate the total of a lineitem's invoice create function recalc() returns trigger as $$ declare r record; begin -- use "new" lineitems record for insert/update, or "old" if delete if (tg_op = 'DELETE') then r = old; else r = new; end if; -- update invoice using lineitems(now "r")'s invoice_id update invoices set total = ( select sum(quantity * price) from lineitems join items on lineitems.item_id = items.id where invoice_id = r.invoice_id) where id = r.invoice_id; -- must return incoming "new" or "old" record when done return r; end; $$ language plpgsql; -- run this function after any change to lineitems create trigger recalc after insert or update or delete on lineitems for each row execute procedure recalc();
现在测试添加、更新和删除，注意它们都会更新 Invoice 总计：
insert into lineitems (invoice_id, item_id, quantity) values (1, 2, 1); select * from lineitems; select * from invoices; select 'Notice new total when you update quantity:' look; update lineitems set quantity = 5 where invoice_id = 1 and item_id = 2; select * from lineitems; select * from invoices; select 'Notice new total when you delete:' look; delete from lineitems where invoice_id = 1 and item_id = 2; select * from lineitems; select * from invoices;
凉爽的？您会获得更多的安全性和安心，因为您知道无论什么代码更新您的数据库，相关字段都会自动更新。
在此处下载最终示例文件： /code/recalc.sql 。
在你让这个示例运行之后，让我们尝试另一个将数据逻辑放入数据库的示例，方法是创建用于更新购物车的函数。
我也使用触发器来清理数据，但这是我很快会写的另一个主题。