触发器实现唯一性约束问题CREATE OR REPLACE TRIGGER tg_temp
BEFORE INSERT OR UPDATE ON temp
FOR EACH ROW
DECLARE
vv_count INTEGER;
exp_errors EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO vv_count
FROM temp t
WHERE t.id =:new.id;
dbms_output.put_line(vv_count);
IF vv_count!=0 THEN
RAISE exp_errors;
ELSE
INSERT INTO temp VALUES(:new.id,:new.name);
END IF;
EXCEPTION
WHEN exp_errors THEN
dbms_output.put_line('插入错误!');
WHEN OTHERS THEN
dbms_output.put_line('其他错误!');
END tg_temp;
测试语句:INSERT INTO temp VALUES(6,'f');
我测试输入了vv_count
结果:0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
其他错误!
为什么会循环输入的,请问如何修改trigger,才可以实现功能!
BEFORE INSERT OR UPDATE ON temp
FOR EACH ROW
DECLARE
vv_count INTEGER;
exp_errors EXCEPTION;
BEGIN
SELECT COUNT(*)
INTO vv_count
FROM temp t
WHERE t.id =:new.id;
dbms_output.put_line(vv_count);
IF vv_count!=0 THEN
RAISE exp_errors;
ELSE
INSERT INTO temp VALUES(:new.id,:new.name);
END IF;
EXCEPTION
WHEN exp_errors THEN
dbms_output.put_line('插入错误!');
WHEN OTHERS THEN
dbms_output.put_line('其他错误!');
END tg_temp;
测试语句:INSERT INTO temp VALUES(6,'f');
我测试输入了vv_count
结果:0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
0
其他错误!
为什么会循环输入的,请问如何修改trigger,才可以实现功能!
RAISE exp_errors;
ELSE
INSERT INTO temp VALUES(:new.id,:new.name);
END IF;这里也是错的,不需要insert into这个语句, 递归了。
RAISE exp_errors;
ELSE
INSERT INTO temp VALUES(:new.id,:new.name);
END IF; 这里也是错的, 不需要insert into这个语句, 递归了。
对,那我如果想实现唯一约束行功。能该怎么修改呢?
要不参考下面的表级触发器,若有重复,output错误提示,可以选择回滚
CREATE OR REPLACE TRIGGER tg_temp
after INSERT OR UPDATE ON temp
--FOR EACH ROW
DECLARE
vv_count integer;
BEGIN
SELECT max(COUNT(*))
INTO vv_count
FROM temp t
group by id;
dbms_output.put_line(vv_count);
IF vv_count>1 THEN
dbms_output.put_line('插入错误!');
END IF;
END tg_temp;
IF vv_count!=0 THEN
RAISE exp_errors;
ELSE
INSERT INTO temp VALUES(:new.id,:new.name);
END IF;
这是递归的原因是什么?
你这里又insert了一把,相当于又触发了这个trigger