How to activate oracle database trigger

Oracle database triggers are activated during DDL (such as CREATE, ALTER) or DML (such as INSERT, UPDATE, DELETE) operations: DDL operations activate triggers defined on the underlying table. DML operations activate triggers defined on the underlying table.

Oracle database trigger activation

Method to activate trigger

Oracle database triggers can be automatically activated in the following two situations:

1. DDL operations: When DDL operations such as CREATE, ALTER, TRUNCATE, etc. are performed on the underlying table, The trigger defined on the table will be activated.
2. DML operations: When DML operations such as INSERT, UPDATE, DELETE, etc. are performed on the underlying table, the triggers defined on the table will be activated.

Trigger activation example

To illustrate trigger activation, we create a simple example table and a trigger defined on the table:

<code class="sql">-- 创建示例表
CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  salary NUMBER
);

-- 定义触发器
CREATE TRIGGER salary_check BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
  IF NEW.salary < 0 THEN
    RAISE_APPLICATION_ERROR(-20001, 'Salary cannot be negative');
  END IF;
END;</code>

Now when we try to update the employee table using DML operation, the trigger will be activated and executed:

<code class="sql">-- 尝试更新员工的薪水为负值
UPDATE employees SET salary = -100 WHERE id = 1;

-- 触发器将引发错误并回滚操作
ORA-20001: Salary cannot be negative</code>

Points to note

• Triggers on each table will only be activated on specific DML or DDL operations.
• Triggers can be defined to activate BEFORE (before the operation is executed) or AFTER (after the operation is executed).
• Triggers can be activated after INSERT, UPDATE, DELETE or DDL operations.
• Triggers can be used to enforce integrity constraints, execute business logic, or record changes to a table.

The above is the detailed content of How to activate oracle database trigger. For more information, please follow other related articles on the PHP Chinese website!