我先在oracle8.1.7的sql*plus中用
CREATE TABLE "SYSTEM"."TEST_B"("NAME" CHAR(10) NOT NULL, "SCORE" NUMBER(3, 1))
建立了Table Test_B;
后又通过
CREATE OR REPLACE TRIGGER "SYSTEM"."TRIGGER_STATE" AFTER INSERT OR
DELETE OR
UPDATE OR
INSERT
ON "TEST_B" FOR EACH ROW
DECLARE
V_TOTAL NUMBER;
BEGIN
SELECT count(*) total_name INTO V_TOTAL from Test_B;
IF (V_TOTAL>5) THEN
DELETE FROM test_state;
INSERT INTO test_state_bak (total_name)
VALUES (V_TOTAL);
END IF;
END TRIGGER_STATE ;
建立了触发器TRIGGER_STATE;
但当执行INSERT INTO TEST_B(NAME) VALUES('f');时
在sql*plus中会出现如下的提示
ORA-04091: 表格 SYSTEM.TEST_B 正在变更中, 触发程序/函数无法检视它
ORA-06512: 在 "SYSTEM.TRIGGER_STATE", line 4
ORA-04088: 执行触发程序 'SYSTEM.TRIGGER_STATE' 时发生错误
请高手指点﹐谢谢﹗
CREATE TABLE "SYSTEM"."TEST_B"("NAME" CHAR(10) NOT NULL, "SCORE" NUMBER(3, 1))
建立了Table Test_B;
后又通过
CREATE OR REPLACE TRIGGER "SYSTEM"."TRIGGER_STATE" AFTER INSERT OR
DELETE OR
UPDATE OR
INSERT
ON "TEST_B" FOR EACH ROW
DECLARE
V_TOTAL NUMBER;
BEGIN
SELECT count(*) total_name INTO V_TOTAL from Test_B;
IF (V_TOTAL>5) THEN
DELETE FROM test_state;
INSERT INTO test_state_bak (total_name)
VALUES (V_TOTAL);
END IF;
END TRIGGER_STATE ;
建立了触发器TRIGGER_STATE;
但当执行INSERT INTO TEST_B(NAME) VALUES('f');时
在sql*plus中会出现如下的提示
ORA-04091: 表格 SYSTEM.TEST_B 正在变更中, 触发程序/函数无法检视它
ORA-06512: 在 "SYSTEM.TRIGGER_STATE", line 4
ORA-04088: 执行触发程序 'SYSTEM.TRIGGER_STATE' 时发生错误
请高手指点﹐谢谢﹗
• 读或修改触发语句的任何变异表,其中包括触发表本身。
• 读或修改触发表的约束表中的主关键字,唯一关键字和外部关键字列。除此之外的其他列
可以修改。
上述限制适用于所有的行级触发器。解决该问题的方法是创建两个触发器,即一个行级触发器和一个语句级触发器。在行级触发器中,我们记录: n e w的值,但我们不查询变异表;而查询任务由语句级触发器来实现并使用行触发器记录的值。
AFTER INSERT OR
DELETE OR
UPDATE OR
INSERT
怎么两个insert?
CREATE OR REPLACE TRIGGER TRIGGER_STATE
before INSERT OR DELETE OR UPDATE ON TEST_B
FOR EACH ROW
DECLARE
V_TOTAL NUMBER;
BEGIN
SELECT count(*) INTO V_TOTAL from Test_B;
IF (V_TOTAL>4) THEN
DELETE FROM test_state;
INSERT INTO test_state_bak (total_name) VALUES (V_TOTAL);
END IF;
END TRIGGER_STATE ;
/
方法二:
CREATE OR REPLACE TRIGGER TRIGGER_STATE
after INSERT OR DELETE OR UPDATE ON TEST_B
DECLARE
V_TOTAL NUMBER;
BEGIN
SELECT count(*) INTO V_TOTAL from Test_B;
IF (V_TOTAL>5) THEN
DELETE FROM test_state;
INSERT INTO test_state_bak (total_name) VALUES (V_TOTAL);
END IF;
END TRIGGER_STATE ;
/