时间戳

首页 > 解决方案 > 如何在 PL/SQL 中编写语句级触发器

sql - 如何在 PL/SQL 中编写语句级触发器

问题描述

CREATE TABLE faculty
(f_id NUMBER(6),
f_last VARCHAR2(15),
f_first VARCHAR2(15),
f_mi CHAR(1),
loc_id NUMBER(5) not null,
f_phone VARCHAR2(10),
f_rank VARCHAR2(9),
f_salary number(9,2), 
f_super NUMBER(6), --supervisor/manager of the faculty member
f_pin NUMBER(4),
f_image BLOB, 
CONSTRAINT faculty_f_id_pk PRIMARY KEY(f_id),
CONSTRAINT faculty_loc_id_fk FOREIGN KEY (loc_id) REFERENCES location(loc_id));

--- inserting records into FACULTY
INSERT INTO faculty VALUES
(1, 'Marx', 'Teresa', 'J', 9, '4075921695', 'Associate', 75000.00, 4, 6338, EMPTY_BLOB());

INSERT INTO faculty VALUES
(2, 'Zhulin', 'Mark', 'M', 10, '4073875682', 'Full', 98000.00, NULL, 1121, EMPTY_BLOB());

INSERT INTO faculty VALUES
(3, 'Langley', 'Colin', 'A', 12, '4075928719', 'Assistant', 60000.00, 4, 9871, EMPTY_BLOB());

INSERT INTO faculty VALUES
(4, 'Brown', 'Jonnel', 'D', 11, '4078101155', 'Full', 102000.00, NULL, 8297, EMPTY_BLOB());

INSERT INTO faculty VALUES
(5, 'Sealy', 'James', 'L', 13, '4079817153', 'Associate', 80000.00, 1, 6089, EMPTY_BLOB());

我已经写了这个 ROW Level 触发器（在下面），但不知道如何获得一个可以做同样事情的语句级触发器。并且因为不能在语句级别使用 :NEW / :OLD ，所以很困惑......

--Row Level
create or replace TRIGGER TRG_F_SALARY_CHECK
BEFORE INSERT OR UPDATE OF F_SUPER ON FACULTY
FOR EACH ROW

BEGIN
    IF :NEW.F_SUPER > :OLD.F_SUPER THEN
        :NEW.F_SALARY := :OLD.F_SALARY + 2000*(:NEW.F_SUPER - 5);
        DBMS_OUTPUT.PUT_LINE('The salary has been updated');
    END IF;
    IF :NEW.F_SUPER < :OLD.F_SUPER THEN
        IF :OLD.F_SUPER >=5 THEN
            IF :NEW.F_SUPER < 5 THEN
                :NEW.F_SALARY := :OLD.F_SALARY - 2000*(:OLD.F_SUPER - 5);
                DBMS_OUTPUT.PUT_LINE('The salary has been updated');
            ELSE
              :NEW.F_SALARY := :OLD.F_SALARY - 2000*(:OLD.F_SUPER - :NEW.F_SUPER);
              DBMS_OUTPUT.PUT_LINE('The salary has been updated');
            END IF;
        END IF;
    END IF;
END;

create or replace TRIGGER TRG_F_SALARY_CHECK_STATEMENT
BEFORE INSERT OR UPDATE OF F_SUPER, F_SALARY ON FACULTY
DECLARE
  --  OLD_F_SUPER NUMBER(10);
    OLD_F_SALARY NUMBER(9);
BEGIN
--        SELECT F_SUPER INTO OLD_F_SUPER FROM FACULTY;
--       SELECT SUM(F_SALARY) INTO OLD_F_SALARY FROM FACULTY
--       WHERE F_ID;
   UPDATE FACULTY
   /* SET F_SALARY = F_SALARY + 2000*(F_SUPER -5)
        WHERE F_SUPER > 5;*/
        SET F_SALARY = OLD_F_SALARY + 2000*(F_SUPER - 5)
            WHERE F_SUPER > 5;
END;

顺便说一句，表FACULTY包括 in F_ID、F_SALARY、F_SUPER等。我将获得 ** 单行触发器 **（已经完成）和一个语句触发器F_SUPER，用于通过更改数字来更新教师的薪水。对于F_SUPER5 之后的每一次更改（增加或减少），F_SALARY将更改（增加或减少）2000 美元。

测试代码如下：

/*F_ID = 5, F_SUPER = 7, F_SALARY = 84000*/
UPDATE FACULTY
SET F_SUPER = 7
WHERE F_ID = 5;
/*F_ID =5, F_SUPER = 2, F_SALARY = 80000*/ 
UPDATE FACULTY
SET F_SUPER = 2
WHERE F_ID = 5;

谢谢

标签： sqloracleplsqltriggers

解决方案

我已经写了这个 ROW Level 触发器（在下面），但不知道如何获得一个可以做同样事情的语句级触发器。并且因为不能在语句级别使用 :NEW / :OLD ，所以很困惑......

语句级触发器和行级触发器是不一样的。它们由 Oracle 根据使用情况提供。因此，不能用语句级触发器替换所有行级触发器。请参阅下面的定义和用法：

数据相关活动的行级触发器

• 行级触发器对事务中的每一行执行一次。

• 行级触发器是最常见的触发器类型。它们通常用于数据审计应用程序中。

• 行级触发器由 CREATE TRIGGER 命令中的 FOR EACH ROW 子句标识。

事务相关活动的语句级触发器

• 语句级触发器对每个事务执行一次。例如，如果单个事务将 500 行插入到 Customer 表中，则该表上的语句级触发器将只执行一次。

• 因此，语句级触发器不常用于与数据相关的活动。它们通常用于对可能在表上执行的事务类型实施额外的安全措施。

• 语句级触发器是创建的默认触发器类型，通过在 CREATE TRIGGER 命令中省略 FOR EACH ROW 子句来标识。

语句级触发器示例：

CREATE or REPLACE TRIGGER Before_Update_Stat_product 
BEFORE UPDATE ON product 
Begin 
  INSERT INTO table  
  Values('Before update, statement level',sysdate); 
END; 
/

推荐阅读