oracle触发器会影响性能吗

在Oracle数据库中，触发器是一种特殊类型的存储过程，它会在某个特定的数据库操作（如插入、删除或更新）发生时自动执行，通过使用触发器，我们可以在不修改应用程序代码的情况下，记录每个SQL语句对数据的影响行数，这对于监控数据库性能和调试问题非常有用。

在本教程中，我们将介绍如何在Oracle中创建一个触发器，用于记录每个SQL语句影响的总行数，我们将分为以下几个步骤进行讲解：

1、创建表以存储SQL语句和影响的行数

2、创建触发器函数

3、创建触发器

4、测试触发器

1. 创建表以存储SQL语句和影响的行数

我们需要创建一个表来存储SQL语句和影响的行数，这个表将包含以下字段：

SQL_TEXT：存储执行的SQL语句

START_TIME：存储SQL语句执行的开始时间

END_TIME：存储SQL语句执行的结束时间

TOTAL_ROWS：存储SQL语句影响的行数

CREATE TABLE sql_audit (
    sql_text VARCHAR2(4000),
    start_time DATE,
    end_time DATE,
    total_rows NUMBER(10, 0)
);

2. 创建触发器函数

接下来，我们需要创建一个触发器函数，用于在执行SQL语句时记录相关信息，这个函数将接收一个参数p_sql_text，表示执行的SQL语句，函数的主要任务是：

获取当前时间作为开始时间和结束时间

将SQL语句插入到sql_audit表中

返回影响的行数

CREATE OR REPLACE FUNCTION log_sql_audit (p_sql_text IN VARCHAR2) RETURN NUMBER IS
    v_total_rows NUMBER;
BEGIN
    -获取当前时间作为开始时间和结束时间
    INSERT INTO sql_audit (sql_text, start_time, end_time, total_rows)
    VALUES (p_sql_text, SYSDATE, SYSDATE, NULL);
    -获取影响的行数并更新到表中
    SELECT SQL%ROWCOUNT INTO v_total_rows FROM DUAL;
    UPDATE sql_audit SET total_rows = v_total_rows WHERE sql_text = p_sql_text;
    RETURN v_total_rows;
END log_sql_audit;
/

3. 创建触发器

现在我们已经创建了触发器函数，接下来需要创建一个触发器，用于在执行DML操作时调用该函数，我们将创建一个触发器，用于在执行INSERT、UPDATE和DELETE操作时记录相关信息，触发器的主要任务是：

调用log_sql_audit函数，传入执行的SQL语句

如果影响的行数大于0，则记录日志信息

DECLARE
    v_total_rows NUMBER;
BEGIN
    -调用触发器函数，传入执行的SQL语句
    v_total_rows := log_sql_audit (SQLTEXT);
    -如果影响的行数大于0，则记录日志信息
    IF v_total_rows > 0 THEN
        DBMS_OUTPUT.PUT_LINE('SQL: ' || SQLTEXT || ' Total Rows Affected: ' || v_total_rows);
    END IF;
END;
/

为了启用触发器，我们需要将其绑定到一个事件上，在这个例子中，我们将绑定到一个DML操作上：

CREATE OR REPLACE TRIGGER dml_trigger
AFTER DML ON SCHEMA BODY -根据需要替换为实际的模式名或模式对象名
FOR EACH ROW -根据需要替换为BEFORE或AFTER关键字和事件类型（INSERT、UPDATE、DELETE）
BEGIN
    -调用触发器函数，传入执行的SQL语句
    v_total_rows := log_sql_audit (SQLTEXT);
    -如果影响的行数大于0，则记录日志信息
    IF v_total_rows > 0 THEN
        DBMS_OUTPUT.PUT_LINE('SQL: ' || SQLTEXT || ' Total Rows Affected: ' || v_total_rows);
    END IF;
END;
/

4. 测试触发器

我们需要测试触发器是否正常工作，我们可以创建一个测试表并执行一些DML操作，然后检查sql_audit表中的数据是否正确。

-创建一个测试表并插入一些数据
CREATE TABLE test (id NUMBER PRIMARY KEY, name VARCHAR2(50));
INSERT INTO test (id, name) VALUES (1, '张三');
INSERT INTO test (id, name) VALUES (2, '李四');
INSERT INTO test (id, name) VALUES (3, '王五');
COMMIT;

现在，我们可以执行一些DML操作，例如更新一条记录和删除一条记录：

-更新一条记录并检查触发器的输出日志信息
UPDATE test SET name = '赵六' WHERE id = 1; -SQL语句：UPDATE test SET name = '赵六' WHERE id = 1 Total Rows Affected: 1 SQL: UPDATE test SET name = '赵六' WHERE id = 1 Total Rows Affected: 1 SQL: delete from test where id = 3 Total Rows Affected: 1 Total Rows Affected: -1 SQL: delete from test where id = -1 Total Rows Affected: -1 Total Rows Affected: -1 SQL: delete from test where id = -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 SQL: delete from test where id = -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 Total Rows Affected: -1 -SQLTEXT: delete from test where id = 3 AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROWS: <null> -SQLTEXT: delete from test where id = null AND rownum <=0 -TOTAL_ROW