ITPub博客

首页 > Linux操作系统 > Linux操作系统 > Trigger Body

Trigger Body

原创 Linux操作系统 作者:yanggq 时间:2019-05-01 21:09:05 0 删除 编辑

The trigger body is written in PL/SQL. There are PL/SQL expressions that only have meaning within a trigger body. These topics are covered in the following paragraphs.

A trigger body can refer to two states of a column value. The syntax for each state is:

:NEW.COLUMN_NAME
:OLD.COLUMN_NAME

:NEW.COLUMN_NAME is the syntax for referencing a column value within the body of the row INSERT and UPDATE triggers. This expression evaluates to NULL in DELETE row triggers. It evaluates to the value included in the SQL INSERT or UPDATE statement.

If the SQL statement does not reference a column, the following rules apply:

  • For INSERT statements, :NEW.COLUMN_NAME is either NULL or the value of the DEFAULT used in creating the table.

  • For UPDATE statements, :NEW.COLUMN_NAME is the value currently in the table.

:OLD.COLUMN_NAME is valid for UPDATE and DELETE row triggers only. This evaluates to NULL for INSERT triggers. The :OLD.COLUMN_NAME evaluates to the value currently in the table.

The values of :OLD.COLUMN_NAME and :NEW.COLUMN_NAME expressions are identical for BEFORE and AFTER row triggers. The choice of a BEFORE or AFTER row trigger is a choice to execute the trigger before or after Oracle constraint enforcement. Visibility to :OLD and NEW values is identical with each. One caveat is that you can change the value of :NEW.COLUMN_NAME in BEFORE row triggers, not in AFTER row triggers.

The following illustrates a table that has BEFORE UPDATE and AFTER UPDATE row triggers. It prints the old and after column values.

CREATE TABLE TEMP(N NUMBER, M NUMBER DEFAULT 5);
CREATE OR REPLACE TRIGGER temp_bur
BEFORE UPDATE ON TEMP
FOR EACH ROW
BEGIN
    dbms_output.put_line('BUR old N:'||:old.n|| ' M:'||:old.M);
    dbms_output.put_line('BUR new N:'||:new.n|| ' M:'||:new.M);
END;

CREATE OR REPLACE TRIGGER temp_aur
AFTER UPDATE ON TEMP
FOR EACH ROW
BEGIN
    dbms_output.put_line('AUR old N:'||:old.n|| ' M:'||:old.M);
    dbms_output.put_line('AUR new N:'||:new.n|| ' M:'||:new.M);
END;

The OLD and NEW values are the same for BEFORE and AFTER row triggers.

SQL> INSERT INTO TEMP (n) VALUES (1);
SQL> UPDATE TEMP SET n=n+1 WHERE n>=1;
BUR old: N=1 M=5
BUR new: N=2 M=5
AUR old: N=1 M=5
AUR new: N=2 M=5
SQL> UPDATE TEMP SET m=2 WHERE M=5;
BUR old: N=2 M=5
BUR new: N=2 M=2
AUR old: N=2 M=5
AUR new: N=2 M=2

The BEFORE UPDATE row trigger is changed. It increments the :NEW.N. This effectively changes the value inserted into the database. This trigger has one line added, the statement to increment :NEW.N.

CREATE OR REPLACE TRIGGER temp_bur
BEFORE UPDATE ON TEMP
FOR EACH ROW
BEGIN
    :NEW.N := :NEW.N + 1;
    dbms_output.put_line('BUR old N:'||:old.n|| ' M:'||:old.M);
    dbms_output.put_line('BUR new N:'||:new.n|| ' M:'||:new.M);
END;

The results of the same INSERT and UPDATE statements show that N was inserted into the database with an incremented value. The first UPDATE in the following statement increments: N to N + 1, from 1 to 2. The trigger increments N again to a value of 3. The second UPDATE statement does not reference the column N, yet it is incremented in the column.

SQL> INSERT INTO TEMP (n) VALUES (1);
SQL> UPDATE TEMP SET n=n+1 WHERE n>=1;
BUR old: N=1 M=5
BUR new: N=3 M=5
AUR old: N=1 M=5
AUR new: N=3 M=5
SQL> UPDATE TEMP SET m=2 WHERE M=5;
BUR old: N=3 M=5
BUR new: N=4 M=2
AUR old: N=3 M=5
AUR new: N=4 M=2

来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/26706/viewspace-64559/,如需转载,请注明出处,否则将追究法律责任。

请登录后发表评论 登录
全部评论

注册时间:2002-10-29

  • 博文量
    78
  • 访问量
    50747