光标在触发器中 [英] cursor in a trigger
问题描述
我有一个现有的表 postn_matrix
,其中包含员工列表和他们的计数。
I have an existing TABLE postn_matrix
which contains a list of employees and a count of their resp. positions in the organisation.
每当添加或删除用户的位置时,相应的计数会反映在表触发此触发器(VIA UPDATE)
Whenever a position of a user is added or removed, the corresponding count is reflected in the table thro' this trigger (VIA UPDATE)
现在,如果有一个新用户,他将不会在 postn_matrix
中有一个条目,因此我必须插入一个新的记录(VIA INSERT)。这需要从BASE表。
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.
更新似乎工作正常,但我不能带来一个新的用户进入表。
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
/
推荐答案
由于循环(它缺少一个退出子句 - 希望你失去了将它转换为一个问题),你将尝试插入一个记录到 pstn_matrix
为每个记录游标返回,是否有任何匹配:new.person_id
如果有匹配,你也会做更新
。这可能不是你想要的,你可能会得到约束违反其他事情。你也不是设置你的计数器字段 - 如果那不是nullable那将会错误。但你没有说出错误,如果有的话,你会得到。
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_matrix
for every record the cursor returns, whether there are any matching :new.person_id
or 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.
如果你必须这样做通过触发器,那么你可以检查是否有一行新人:
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
.
但是我不喜欢这个模型,你正在设置潜在的数据差异和并发修改基表。尝试保持这样的计数不一定像看起来那么简单。
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;
当然,我可能会曲解或过度简化您的基本表, postn_matrix
for。看起来有点琐碎,甚至作为一个视图。如果你有单独的 person
和 person_position
表,那么你可以添加一个外连接来查看没有位置:
Of course, I may be misinterpreting or oversimplifying what your base table(s) does and what you need postn_matrix
for. It seems a little trivial to have even as a view. If you have separate person
and person_position
tables, say, then you can add in an outer join to see people with no positions:
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;
这篇关于光标在触发器中的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!