数据库插入触发器 sql->oracle

来源：互联网发布：java spring框架编辑：程序博客网时间：2024/04/27 22:51

sql 脚本

alter trigger tri

on HR_PunchRecord
instead of insert
as
declare @FRegNo varchar(20)
declare @FEmpCode varchar(20)
declare @FPunchID varchar(20)
declare @FVerifyMode varchar(20)
declare @FDate varchar(20)
declare @FTime varchar(20)
declare @FPhoto varchar(20)

select @FRegNo=FRegNo,@FEmpCode=FEmpCode ,@FPunchID=FPunchID ,@FVerifyMode=FVerifyMode ,@FDate=FDate ,@FTime=FTime ,@FPhoto=FPhoto from inserted

if exists (select 1 from HR_PunchRecord where FRegNo=@FRegNo and FPunchID=@FPunchID and FDate=@FDate and FTime=@FTime)
begin
--print'存在'
rollback transaction
end
else
begin
insert into HR_PunchRecord ( HR_PunchRecord.FRegNo,HR_PunchRecord.FEmpCode,HR_PunchRecord.FPunchID,
HR_PunchRecord.FVerifyMode,HR_PunchRecord.FDate, HR_PunchRecord.FTime,
HR_PunchRecord.FPhoto, HR_PunchRecord.FIsNew)
values
(@FRegNo,@FEmpCode,@FPunchID,@FVerifyMode,@FDate,@FTime,@FPhoto,1)
end

oracle

CREATE OR REPLACE TRIGGER TriggerInsertOrUpdate2
BEFORE INSERT ON HR_PUNCHRECORD
FOR EACH ROW
DECLARE
vCount NUMBER(1);
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
SELECT COUNT(1) INTO vCount FROM HR_PUNCHRECORD WHERE FREGNO = :NEW.FREGNO and FPUNCHID = :NEW.FPUNCHID and FDATE = :NEW.FDATE and FTIME = :NEW.FTIME;
--DBMS_OUTPUT.PUT_LINE(vCount);
IF vCount = 1 THEN
DELETE FROM HR_PUNCHRECORD WHERE FREGNO = :New.FREGNO and FPUNCHID = :NEW.FPUNCHID and FDATE = :NEW.FDATE and FTIME = :NEW.FTIME;
END IF;
commit;
END TriggerInsertOrUpdate2;

参考：http://blog.sina.com.cn/s/blog_7cc3b8db010113oj.html

0 0