Oracle 触发器中获取当前行数据

标签: Oracle
发布时间: 2017/4/15 18:27:04
注意事项: 本文中文内容可能为机器翻译,如要查看英文原文请点击上面连接.

我有 Oracle 10g 有一个插入触发事件后的每一行,我想要中插入的当前行触发一些逻辑为基础的触发器。 我想获取用户桌旗被插入的列的值,然后在该触发器,使用如果-那么-否则做某事,只有当标志为空。如何让国旗被插入的行的列的值有什么想法? 我的目标是逻辑的不做任何以下触发器中标志列的值为 null 时。

Table: USERS

Columns:
id (PK generated from a DB sequence)
.... (more columns)
flag VARCHAR2(1 BYTE) and is nullable

触发器︰

//goal is to not do any of the logic in the trigger below when flag is null


CREATE OR REPLACE TRIGGER "DBUSER"."TI_USERS" 
  AFTER INSERT
  on Users

  for each row

declare numrows INTEGER;
begin
    select count(*) into numrows
      from Customer
      where
        /* %JoinFKPK(:%New,Customer," = "," and") */
        :new.Customer_Key = Customer.Customer_Key;
    if (
      /* %NotnullFK(:%New," is not null and") */

      numrows = 0
    )
    then
      raise_application_error(
        -20002,
        'Cannot INSERT Users because Customer does not exist.'
      );
    end if;



end;
ALTER TRIGGER "SIMPLEX"."TI_USERS" ENABLE

解决方法 1:

CREATE OR REPLACE TRIGGER "DBUSER"."TI_USERS" 
  AFTER INSERT
  on Users

  for each row

declare numrows INTEGER;
begin

IF ( :new.flag IS NOT NULL ) Then

    select count(*) into numrows
      from Customer
      where
        /* %JoinFKPK(:%New,Customer," = "," and") */
        :new.Customer_Key = Customer.Customer_Key;
    if (
      /* %NotnullFK(:%New," is not null and") */

      numrows = 0
    )
    then
      raise_application_error(
        -20002,
        'Cannot INSERT Users because Customer does not exist.'
      );
    end if;

end if;

end;
ALTER TRIGGER "SIMPLEX"."TI_USERS" ENABLE
赞助商