What is the difference between a stored procedure, a function and a trigger?
What is the difference between a stored procedure, a function and a trigger?
All three are code stored in the database, but they are invoked differently and have different purposes.
| Stored procedure | Function | Trigger | |
|---|---|---|---|
| Invoked by | CALL / EXEC explicitly |
inside an expression (SELECT f(x)) |
automatically on INSERT, UPDATE, DELETE (and in some engines DDL or logon events) |
| Returns | nothing, OUT parameters, or result sets |
a value (scalar) or a table | nothing; it may change the row or raise an error |
| Transaction control | often allowed (COMMIT in PostgreSQL 11+ procedures) |
not allowed in most engines | runs inside the triggering statement's transaction |
| Typical use | multi-step business operations, batch jobs | reusable calculations, computed columns, table-valued helpers | auditing, derived-data upkeep, enforcing complex rules |
-- function: used inside queries
CREATE FUNCTION vat(amount NUMERIC) RETURNS NUMERIC
LANGUAGE sql IMMUTABLE AS $$ SELECT round(amount * 0.20, 2) $$;
SELECT id, total, vat(total) FROM orders;
Đăng nhập để tham gia thảo luận cùng mọi người.
Chưa có bình luận nào
Hãy là người đầu tiên chia sẻ suy nghĩ của bạn.