关于存储过程、函数、视图及触发器的存储位置与方式的技术咨询

Great question! Let's break this down clearly, since it's easy to mix up how database objects behave compared to ad-hoc queries or REPL-based code.

核心结论：是的，这些对象会持久化存储 #

Unlike ad-hoc SQL queries (which are executed and discarded immediately) or functions/procedures defined in a programming language's REPL (which disappear when the session closes), stored procedures, functions, views, and triggers are database objects—once you create them successfully, they're persisted to the database server until you explicitly drop them. They survive session closures, server restarts, and even backups/restores (as long as you include the database in those operations).

存储层级：按数据库（大部分情况），而非服务器或单个用户表 #

Nearly all user-created stored procedures, functions, views, and triggers are tied to a specific database, not the entire DBMS server. Here's how it works:

• Server-level objects are rare: Only a small set of system-provided functions/procedures exist at the server level (e.g., SQL Server's sp_helpdb). Almost everything you create as a user is database-specific.
• Tied to a database: When you create one of these objects, you're creating it within the context of your current database (or you can explicitly specify the database in your CREATE statement). If you drop the database, all its associated stored procedures, functions, views, and triggers are deleted too.

存储位置：系统目录（系统表），而非用户数据表格 #

These objects aren't stored in your regular user tables (the ones holding your application data). Instead, every database has a set of system catalogs (or system tables/views) that store metadata about all database objects. Think of these as the DBMS's internal "inventory" of everything in the database.

For example:

• PostgreSQL:
  • Functions/stored procedures: pg_proc
  • Views: pg_views
  • Triggers: pg_trigger
• MySQL:
  • Stored procedures/functions: INFORMATION_SCHEMA.ROUTINES
  • Views: INFORMATION_SCHEMA.VIEWS
  • Triggers: INFORMATION_SCHEMA.TRIGGERS
• SQL Server:
  • Stored procedures: sys.procedures
  • Views: sys.views
  • Triggers: sys.triggers

These system catalogs store details like the object's name, definition (the actual code you wrote), creation date, owner, and dependencies (e.g., which table a trigger is attached to). When you call a stored procedure or query a view, the DBMS looks up this metadata to execute the associated logic.

各对象类型的存储细节快速梳理 #

• Views: Stores the SQL query definition (not the actual result set). The result is computed dynamically every time you query the view.
• Stored procedures/functions: Stores the full code definition, plus any compiled execution plans (depending on the DBMS). Some systems cache these plans for better performance.
• Triggers: Stores the trigger logic and the conditions that trigger it (e.g., AFTER INSERT on a specific table), along with a direct link to the target table.

内容的提问来源于stack exchange，提问作者Tim