这种触发器要怎么写

[cslhg]

reader_history有三个字段,employee_id 是员工号,date_time 充值日期,czje 充值金额,这个表是临时保存数据的
我将表reader_history数据插入到表xwriter_history(员工充值表),但因为在reader_history表有时员工会充值二个一模一样的记录,所以保存到xwriter_history表中也会存在同一工号,在同一时间,充值了一模一样的记录,我想达到这种目的,从reader_history向xwriter_history插入数据的时候,发现reader_history插入的数据(employee_id 是员工号,date_time 充值日期,czje 充值金额)在xwriter_history已有的时候就不插入重复记录,即若二条一模一样的记录只插入一条就行了,如下是我写的但没判断,所以有重复的记录还可插入


create or replace trigger tri_update_xwriter_history
before insert on reader_history
for each row
declare
-- local variables here
begin
if :new.port_number=2 then
insert into xwriter_history values(:new.employee_id,:new.date_time,:new.czje);
where xwriter_history.employee_id = :new.employee_id;
end if;

end tri_update_xwriter_history;

[cslhg]

提示触发器无效,未通过重新确认

[cslhg]

有没人知道是怎么回事

[Eric_1999]

错误是什么也不贴一下啊？
create or replace trigger tri_update_xwriter_history
before insert on reader_history
for each row
declare
-- local variables here
cnt number; --计算变量
begin
--判断有没相同的。
select count(*) into cnt from reader_history where :new.employee_id = employee_id and :new.date_time = new.date_time and :new.czje = czje
if cnt < 1 and :new.port_number=2 then --没相同的才插入
insert into xwriter_history values(:new.employee_id,:new.date_time,:new.czje);
where xwriter_history.employee_id = :new.employee_id;
end if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
dbms_output.put_line('nodata');
end tri_update_xwriter_history;


其实还有一种办法，就是在表中设置一个字段为primary key，那就不用写pl/sql。

[cslhg]

各位老大有没有知道

[Eric_1999]

create or replace trigger tri_update_xwriter_history
before insert on reader_history
for each row
declare
-- local variables here
cnt number; --计算变量
begin
when inserting then
--判断有没相同的。
select count(*) into cnt from reader_history where :new.employee_id = employee_id and :new.date_time = new.date_time and :new.czje = czje
if :new.port_number=2 and cnt < 1 then --没相同的才插入
insert into xwriter_history values(:new.employee_id,:new.date_time,:new.czje);
where xwriter_history.employee_id = :new.employee_id;
end if;
end;

end tri_update_xwriter_history;