PL/SQL行触发器需求：用Salary=salary*1.1更新Employee表薪资

Alright, let's work through creating the PL/SQL row trigger for your Employee table. First, a quick clarification: row-level triggers fire once for every row affected by a DML operation (like INSERT/UPDATE/DELETE). If your goal is to update all employees' salaries when a specific event happens, a statement-level trigger might be more efficient (since it runs once per DML statement, not per row). But since you specifically asked for a row trigger, I'll cover both scenarios to make sure we hit what you need.

If you want the 10% salary increase to apply only to the row being inserted or updated (e.g., boosting a new hire's starting salary by 10%, or adding a 10% bump when an existing employee's salary is modified), here's the trigger code:

CREATE OR REPLACE TRIGGER emp_salary_adjust_row
BEFORE INSERT OR UPDATE OF Salary ON Employee
FOR EACH ROW -- This line defines it as a row-level trigger
BEGIN
    -- Handle INSERT and UPDATE cases separately to avoid NULL issues
    IF INSERTING THEN
        -- For new employees, apply 10% increase to the incoming salary value
        :NEW.Salary := :NEW.Salary * 1.1;
    ELSIF UPDATING THEN
        -- For existing employees, use the old salary to calculate the new value
        :NEW.Salary := :OLD.Salary * 1.1;
    END IF;
END;
/

Key Details: #

• FOR EACH ROW: This is what makes it a row-level trigger—without this, it would be a statement-level trigger.
• :OLD and :NEW: These are special bind variables in PL/SQL triggers. :OLD holds the row's values before the DML operation, :NEW holds the new values being set. For INSERTs, :OLD is NULL, so we handle that case explicitly.
• UPDATE OF Salary: This restricts the trigger to only fire when the Salary column is updated, which prevents unnecessary trigger execution for other column changes.

If your actual goal is to update every employee's salary when any DML operation happens on the Employee table (e.g., when a new employee is added, everyone gets a 10% raise), a statement-level trigger is a better fit. Here's how that looks:

CREATE OR REPLACE TRIGGER emp_salary_adjust_all
AFTER INSERT OR UPDATE OR DELETE ON Employee
BEGIN
    -- Apply 10% increase to all employees in the table
    UPDATE Employee
    SET Salary = Salary * 1.1;
END;
/

Important Warning: #

Be cautious with this trigger—if you run an UPDATE that affects multiple rows, this trigger will fire once and update every row in the table, which could lead to unintended salary changes. Also, Oracle allows recursive triggers by default, so the UPDATE inside the trigger might fire the same trigger again. To prevent this, you can disable recursive triggers with:

ALTER SYSTEM SET RECURSIVE_TRIGGERS = FALSE;

• Permissions: Ensure you have the CREATE TRIGGER privilege on the Employee table before creating the trigger.
• Test First: Always test triggers in a non-production environment. Try inserting a new employee, updating an existing salary, and verify the results match your expectations.
• Performance: Row-level triggers can impact performance if you're running bulk operations (like updating hundreds of rows), since the trigger runs once per row.

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