创建在插入时引发异常的触发器 [英] Creating trigger which throws an exception on insert

问题描述

各位程序员大家好，新年快乐！

Hello fellow programmers and happy new year to you all!

我在寒假期间的大学任务很少，其中之一是在表上创建触发器:

I have few university tasks for winter break and one of them is to create trigger on table:

PERSON(ID, Name, Surname, Age);

触发器应该在用户插入带有无效 ID 的行时通知用户.Vadility 标准是 ID 的长度为 11 位.

Trigger is supposed to inform user when they have inserted row with invalid ID. Vadility criteria is that ID is 11 digits long.

我试着写这样的解决方案:

I tried to write solution like this:

CREATE OR REPLACE TRIGGER person_id_trigg
AFTER INSERT
ON person
DECLARE
  idNew VARCHAR(50);
  lengthException EXCEPTION;
BEGIN
  SELECT id INTO idNew FROM INSERTED;
  IF LENGTH(idNew) <> 11 THEN
  RAISE lengthException;
  END IF;
  EXCEPTION
  WHEN lengthException THEN
  dbms_output.put_line('ID for new person is INVALID. It must be 11 digits long!'); 
END;

然后我意识到INSERTED只存在于sqlserver中，而不存在于oracle中.

Then I realized that INSERTED exists only in sqlserver and not in oracle.

你建议我怎么做才能解决这个问题?

What would you suggest I could do to fix that?

提前致谢！

推荐答案

是否要引发异常(这会阻止插入成功)?或者您是否希望允许插入成功并将字符串写入 dbms_output 缓冲区，该缓冲区可能存在也可能不存在，并且可能会或可能不会向运行插入的人显示?

Do you want to raise an exception (which would prevent the insert from succeeding)? Or do you want to allow the insert to succeed and write a string to the dbms_output buffer that may or may not exist and may or may not be shown to a human running the insert?

无论哪种情况，您都希望这是一个行级触发器，而不是语句级触发器，因此您需要为每一行添加 子句.

In either case, you'll want this to be a row-level trigger, not a statement-level trigger, so you'll need to add the for each row clause.

CREATE OR REPLACE TRIGGER person_id_trigg
  AFTER INSERT
  ON person
  FOR EACH ROW

如果你想引发异常

BEGIN
  IF( length( :new.id ) <> 11 )
  THEN
    RAISE_APPLICATION_ERROR( -20001, 
                             'The new ID value must have a length of 11' );
  END IF;
END;

如果您想潜在地打印输出但允许 insert 成功

If you want to potentially print output but allow the insert to succeed

BEGIN
  IF( length( :new.id ) <> 11 )
  THEN
    dbms_output.put_line( 'The new ID value must have a length of 11' );
  END IF;
END;

当然，实际上，您永远不会为此类事情使用触发器.在现实世界中，您将使用约束.

Of course, in reality, you would never use a trigger for this sort of thing. In the real world, you would use a constraint.

这篇关于创建在插入时引发异常的触发器的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！