oracle 触发器中的光标

cursor in a trigger

提问by MayankG

I have an existing TABLE postn_matrixwhich contains a list of employees and a count of their resp. positions in the organisation.

我有一个现有的 TABLE postn_matrix，其中包含员工列表和他们的人数。在组织中的职位。

Whenever a position of a user is added or removed, the corresponding count is reflected in the table thro' this trigger (VIA UPDATE)

每当添加或删除用户的位置时，相应的计数都会通过此触发器反映在表中（VIA UPDATE）

Now, if there is a new user, he will not have an entry in postn_matrix, so I have to insert a new record for him/her (VIA INSERT). This needs to be brought in from the BASE TABLE.

现在，如果有一个新用户，他将没有条目postn_matrix，所以我必须为他/她插入一个新记录（通过插入）。这需要从 BASE TABLE 中引入。

The update seems to be working fine but I am not able to bring in a new user into the table.

更新似乎运行良好，但我无法将新用户引入表中。

I've been trying to handle this case with a cursor. But it hasnt been of any help yet. I'm hoping some expert could show me the light.. :). any other suggestions besides using a cursor will be much appreciated

我一直在尝试用游标处理这种情况。但这还没有任何帮助。我希望一些专家可以给我看灯.. :)。除了使用游标之外的任何其他建议将不胜感激

CREATE OR REPLACE TRIGGER TRIG1
BEFORE INSERT OR DELETE ON (BASETABLE)
FOR EACH ROW
DECLARE
  cursor c1 is
  select person_id 
  from postn_matrix;

  v_temp varchar2(15);
BEGIN
  IF INSERTING THEN
    open c1;    
    LOOP
      fetch c1 into v_temp;

      if v_temp!=:new.person_id THEN
        insert into POSTN_MATRIX (PERSON_ID)
        VALUES (:new.PERSON_ID);
      else
        UPDATE POSTN_MATRIX
        //this is working fine ;

      END IF;
    end loop;
    close c1;

END
/

回答by Alex Poole

Because of the loop (which is missing an exit clause - hopefully you've just lost that translating this into a question) you're going to attempt to insert a record into pstn_matrixfor everyrecord the cursor returns, whether there are any matching :new.person_idor not; and if there is match you'll also do the update. Which probably isn't what you want, and you might get a constraint violation among other things. You also aren't setting your counter field - if that is not nullable then that will error. But you haven't said what errors, if any, you are getting.

因为循环的（这是缺少退出条款-希望你刚刚失去了这个翻译成问题），你会尝试插入记录pstn_matrix的每一个记录光标回报，是否有任何匹配:new.person_id或不; 如果有匹配项，您也会执行update. 这可能不是您想要的，并且您可能会违反约束。您也没有设置您的计数器字段 - 如果该字段不可为空，则会出错。但是你没有说你得到了什么错误，如果有的话。

If you must do this through a trigger then you can either check if there is a row for the new person at all:

如果您必须通过触发器执行此操作，那么您可以检查是否有新人的行：

DECLARE
  v_temp postn_matrix.person_id%TYPE;
BEGIN
  IF INSERTING THEN
    select max(person_id) into v_temp
    from postn_matrix
    where person_id = :new.person_id;

    if v_temp is null then
      -- no record found, so insert one
      insert into postn_matrix (person_id, position_count)
      values (:new.person_id, 1);
    else
      -- record exists, so update
      update postn_matrix ...
    end if;
  ...

... or use merge.

... 或使用merge.

But I don't like this model, and you're setting up the potential for data discrepancies with concurrent modifications to the base table. Trying to maintain a count like this is not necessarily as simple as it seems.

但我不喜欢这种模型，而且您正在设置对基表进行并发修改时出现数据差异的可能性。试图保持这样的计数并不一定像看起来那么简单。

I'd usually prefer to make this a view, which will always be up to date and doesn't need the trigger complicating things:

我通常更愿意将此作为视图，它始终是最新的并且不需要触发器使事情复杂化：

create view postn_matrix as
  select person_id, count(*)
  from basetable
  group by person_id;

Of course, I may be misinterpreting or oversimplifying what your base table(s) does and what you need postn_matrixfor. It seems a little trivial to have even as a view. If you have separate personand person_positiontables, say, then you can add in an outer join to see people with no positions:

当然，我可能会误解或过度简化您的基表的作用以及您的需要postn_matrix。即使作为一个视图，它似乎也有点微不足道。例如，如果您有单独的表格person和person_position表格，那么您可以添加一个外部联接来查看没有职位的人：

create view postn_matrix as
  select p.person_id, count(pp.position_id)
  from person p
  left join person_position pp on pp.person_id = p.person_id
  group by p.person_id;