我要做的是给一个organ表插入ORGANCODE的工作,建表语句如下:create table ORGAN
(
ID NUMBER not null,
ORGAN_FATHER NUMBER default (0), --父机构,等于-1的话表示最高机构
ORGANCODE VARCHAR2(100)
)这个表主要表示机构的层次关系,ID、ORGANCODE和ORGAN_FATHER如下:
ID ORGANCODE ORGAN_FATHER
1 000 -1
2 000001 1
3 000001001 2
4 000002 1我现在要做的就是在插入新的数据后,插入的字段为id,ORGAN_FATHER,然后通过触发器自动生成ORGANCODE插入表中。但是发现了变异表的问题。
我的触发器如下:create or replace trigger organcode
after insert or update of organ_father on organ
for each row
declare
f number;
begin
if INSERTING
THEN
update organ set organcode='000003' where id=:NEW.id;
-- 查询也报错...
--select organ_father into f from organ where id=:new.id;
Dbms_Output.put_line(f);
--test_insert3(:NEW.id);
--ELSIF UPDATING
--THEN
--test4(:OLD.id);
END IF;
end organcode;插入语句如下:
insert into organ(id,organ_father) values(3,1);
之后就报错了,不知道要怎么解决啊,我看过资料,说是变异表的我能提,用什么语句级和行级触发器分开写,然后写个包,但没怎么明白。
希望能提供些解决问题的实际代码,谢谢了。
(
ID NUMBER not null,
ORGAN_FATHER NUMBER default (0), --父机构,等于-1的话表示最高机构
ORGANCODE VARCHAR2(100)
)这个表主要表示机构的层次关系,ID、ORGANCODE和ORGAN_FATHER如下:
ID ORGANCODE ORGAN_FATHER
1 000 -1
2 000001 1
3 000001001 2
4 000002 1我现在要做的就是在插入新的数据后,插入的字段为id,ORGAN_FATHER,然后通过触发器自动生成ORGANCODE插入表中。但是发现了变异表的问题。
我的触发器如下:create or replace trigger organcode
after insert or update of organ_father on organ
for each row
declare
f number;
begin
if INSERTING
THEN
update organ set organcode='000003' where id=:NEW.id;
-- 查询也报错...
--select organ_father into f from organ where id=:new.id;
Dbms_Output.put_line(f);
--test_insert3(:NEW.id);
--ELSIF UPDATING
--THEN
--test4(:OLD.id);
END IF;
end organcode;插入语句如下:
insert into organ(id,organ_father) values(3,1);
之后就报错了,不知道要怎么解决啊,我看过资料,说是变异表的我能提,用什么语句级和行级触发器分开写,然后写个包,但没怎么明白。
希望能提供些解决问题的实际代码,谢谢了。
定义的表。需要明确的是trigger中SQL语句不能进行如下操作:
1)读或修改触发语句的任何变异表,其中包括触发表本身
2)读或修改触发表的约束表中的主关键字,唯一关键字和外部关键字列。除此之外的其他列都可以修改。
create or replace package mypack as
TYPE t_uname is table of ORGAN.ID%type
index by binary_integer;
v_id t_id;
v_row binary_integer:=0;
end mypack;
--第二步:建立一BEFORR的行級觸發器
CREATE OR REPLACE TRIGGER ORGAN_before
before UPDATE of uname ON mytest
FOR EACH ROW
BEGIN
mypack.v_row := mypack.v_row + 1;
mypack.v_id(mypack.v_row) := :new.id;
END ORGAN_before;
--第三步:建立一個AFTER的語句級觸發器
CREATE OR REPLACE TRIGGER ORGAN_after
after UPDATE of uname ON ORGAN
declare
v_id ORGAN.id%TYPE;
BEGIN
FOR V_LOOP IN 1..mypack.v_row LOOP
v_id:= mypack.v_id(V_LOOP);
END LOOP;
--此处,实现你的逻辑
update ORGAN organcode='000003' where id=v_id;
END ORGAN_after;
希望能有帮助
基本上就是上面个思路。1. 定义package存变量,变量是session级别的
2. before的行级trigger 准备变量值
3. after的表级trigger 做真正的dml操作。也可以直接在行级trigger里使用自治事务。
现在是要循环更新,有就是一个机构的父机构改变了organcode,原来这个机构下面的子机构也要跟着改变organcode,但也发生了问题
Have you ever been India ? Can you tell me something about India?
Can you tell me something about you hometown? Describe the procedure of receiving a phone number and a SIM card in a ware.
How to produce a rate plan, a service pack and how to manage their relations between them.
Describe the flow of starting a new subscriber(prepaid or postpaid). Make an introduction of yourself.
Have a little talk about your current working situation. How do you think your work?
What do you think about working overseas. What are you in charge of at the moment?
What do you think of your job?
What do you think is the most important thing in the buisiness you just talked about?
Do you have any idea about Huawei company?
How do you get on with your work mates.
How do you ask for help?
常用句型、词组:
如果,当...则... 当...则... , 不要用 if else if else 这种形式不是很对
1. If you are honest to others, they will be honest to you in turn. When you are sad, they will comfort you, when you are in trouble they will help you.
2. In a word. 一句话说,简单来说。
In a word , this is an really bad idea. 3. at the same time 同时
When you are in sick, you are not feeling well, at the same time, your work will be left behind. 4. regarded as 被看做
I have been regarded as an expert in this area in our comapany. 5. The reason is quite simple and clear. 原因很简单
The result is quite simple and clear now. 现在结果很清楚简单了。 6. In conclusion/to sum up. 结论, 最后做总结时用
In conclusion, I hope everyone can read some English materials at home. 最后,我想说我希望每个人能在加能读一些英语资料。 7. My aim is to become an expert in this area. 我目标是成为一个专家。 8. My aim is to become an expert in this area. However, it needs a lot of efforts. 但是, 需要很多的努力 9. maintain 维护
My job is maintaining the network of our company. 10. In other words, 换句话说 11. No wonder 难怪
His dad is a very successful businessman, and his family is very rich. It's no wonder that many girls like him. / No wonder many girl like him. 难怪每个人都喜欢他 12. It is necessary/essential/fundamental to do that. 很必要去做那件事。 13. I am sure that there are some bugs in this program. 我很确定这个程序有问题。 14. still 仍然
I still studied very hard 15. Except
Except I'm very sick on that day, I never calling in sick.
Though I told them to do it follow the steps, they just don't listen. 虽然我告诉他们按步骤来做,但是他们不听。
Though I had something for lunch, I'm still feeling hungry. 虽然我中午吃了饭, 但是我任然饿 17. That’s my opinion 那是我的建议 18. In the morning,at noon, In the afternoon , At night, At midnight 在早上,上午/中午/下午/晚上/00点 19. acquire knowledge 掌握知识 20. character 性格 个性 kindness 对人很好 funny possitive 积极向上 nagative 悲观的 He rarely talk. 他很少说话 good-natured and treats others sincerely 人很好,很真诚 easy tempered 容易发火的 21. be bound to 一定会
I'm bound to be success 我一定会成功 22. can only
It can only make me sad 那只能让我难过 23. Needless to say 没得说, 不用多说
Needless to say, he is the backbone of this company/ He is the key person/Mr key of this company, 不用说,他是这个公司的骨干 24. As a result 结构、最后
As a result, he won 25. It is quite obvious that.. 很明显
It is quite obvious that he lost. 26. Hit me pretty hard 对我打击很大
This news hits me pretty hard. 27. on top of that 再那之上
I need to pay for the house rent, on top of that, my credit card is also in debt. 除了付房租外, 我的信用卡也欠费了 28. it is of no use to do so. 这样做没用 29. in addition, 另外 30. not only.. but also 不仅 而且
Not only summer is a hot season but also diseases are apt to happen
31. Both ... and ..
Both you and me are indispensable. 你和我都是不可缺少的。 32. try our best to 尽我们最好的
try our best to finish the job. 33. It is of course .. to .. 当然很..
It is of course very tired to stay up too late. 34. No matter 不管
No matter how loudly he is shouting, no one cares about him 不管他喊多大声,每人关心他。 35. as for me 就我而言
As for me, it is not very hard to do those things. 就我而言,做这些事不难