如何在Toad for Oracle 9.7.2中生成含数据、序列及触发器的表脚本

Let's use Oracle for this example—it's the most common database that relies on sequences + triggers to handle auto-incrementing IDs. I'll break down every piece so you can see exactly how they work together.

1. First, Create the Sequence #

This sequence will generate the auto-increment values for your primary key. Here's the code with key parameters explained:

-- 创建自增ID序列
CREATE SEQUENCE users_seq
  START WITH 1          -- 序列起始值
  INCREMENT BY 1        -- 每次递增1
  NOCACHE               -- 不缓存序列值（避免数据库重启后丢失未使用的缓存值）
  NOCYCLE;              -- 序列到达最大值后停止，不循环

2. Create the Table #

Next, define your table with a primary key column that we'll link to the sequence via the trigger:

-- 创建用户表
CREATE TABLE users (
  user_id NUMBER(10) PRIMARY KEY,  -- 自增主键字段
  username VARCHAR2(50) NOT NULL UNIQUE,
  email VARCHAR2(100) NOT NULL UNIQUE,
  created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP  -- 默认当前时间
);

3. Create the Trigger to Link Sequence and Table #

This is the critical part that connects the sequence to your table. The trigger runs before each insert and automatically assigns the next sequence value to the primary key:

-- 创建触发器，插入时自动填充自增ID
CREATE OR REPLACE TRIGGER users_trigger
BEFORE INSERT ON users
FOR EACH ROW  -- 行级触发器：每插入一行就执行一次
BEGIN
  -- 把序列的下一个值赋值给新行的user_id字段
  SELECT users_seq.NEXTVAL INTO :NEW.user_id FROM DUAL;
END;
/

Quick breakdown of the trigger logic: #

• BEFORE INSERT ON users: Tells the database to run this trigger right before an insert operation on the users table.
• FOR EACH ROW: Ensures the trigger runs for every single row being inserted (not just once per insert statement).
• :NEW.user_id: References the user_id column of the row that's about to be inserted. We use users_seq.NEXTVAL to grab the next value from our sequence and assign it here.

4. Insert Test Data (No Need to Specify the ID!) #

Now when you insert data, you don't have to include the user_id—the trigger will handle it automatically:

-- 插入测试数据（无需指定user_id）
INSERT INTO users (username, email) VALUES ('john_doe', 'john@example.com');
INSERT INTO users (username, email) VALUES ('jane_smith', 'jane@example.com');

-- 查询验证数据是否自动生成了ID
SELECT * FROM users;

Extra Notes: #

• If you're using PostgreSQL, you can use SERIAL or IDENTITY as simpler alternatives, but if you still want to use a sequence + trigger, the syntax is similar (you'd use nextval('users_seq') directly in the trigger instead of selecting from DUAL).
• Make sure the user creating the trigger has permission to access the sequence—if not, run GRANT SELECT ON users_seq TO your_username;
• If you ever need to reset the sequence (e.g., after deleting rows), you can use ALTER SEQUENCE users_seq RESTART WITH 1; (Oracle 12c+ supports this; older versions require a workaround).

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