PLSQL触发器相等性校验：插入前检查costumer表用户名是否重复

Hey there! Let's break down how to build that PL/SQL trigger to enforce unique usernames before inserting into your customer table, plus cover a better alternative you might want to consider.

Core Idea #

We'll create a row-level BEFORE INSERT trigger that checks if the username being inserted already exists in the customer table. If a duplicate is found, we'll throw a custom error to block the insert operation.

Full Trigger Code #

CREATE OR REPLACE TRIGGER trg_customer_unique_username
BEFORE INSERT ON customer
FOR EACH ROW
DECLARE
    v_username_exists NUMBER;
BEGIN
    -- Check for existing matching username
    SELECT COUNT(*)
    INTO v_username_exists
    FROM customer
    WHERE username = :NEW.username;

    -- If duplicate found, raise error to stop insert
    IF v_username_exists > 0 THEN
        RAISE_APPLICATION_ERROR(
            -20001, 
            'Error: Username "' || :NEW.username || '" is already taken. Please choose a different one.'
        );
    END IF;
END;
/

Key Details Explained #

• BEFORE INSERT ON customer: Tells the database to run this logic right before any insert operation on the customer table.
• FOR EACH ROW: Makes this a row-level trigger, so it runs once for every row being inserted (works for single inserts and bulk inserts alike).
• :NEW.username: References the username value from the row that's about to be added to the table.
• RAISE_APPLICATION_ERROR: A built-in PL/SQL procedure that lets you throw custom error messages. The error code (-20001 here) must be between -20000 and -20999 to avoid conflicting with Oracle's predefined errors.

A Better Alternative: Unique Constraint #

While the trigger works, the most efficient and maintainable way to enforce unique usernames is to use a database-level unique constraint instead. Here's how to set it up:

ALTER TABLE customer
ADD CONSTRAINT uk_customer_username UNIQUE (username);

Why this is better:

• It's optimized for performance (Oracle handles uniqueness checks natively, faster than a custom trigger).
• It automatically blocks both duplicate inserts AND updates that would create a duplicate username (you'd have to modify the trigger to handle updates if needed).
• No custom PL/SQL code to maintain or debug.

Testing the Trigger #

To verify it works, try inserting a duplicate username:

INSERT INTO customer (username, email, full_name) 
VALUES ('johndoe', 'john@example.com', 'John Doe');

If johndoe already exists, you'll get the custom error message we defined, and the insert will be rolled back.

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