Oracle PL / SQL – UPDATE触发器之前的示例

本文向您展示了如何使用BEFORE UPDATE TRIGGER ，它在执行更新操作之前就可以使用。 在现实生活中，它主要用于以下目的：

1. 资料验证
2. 自动更新值
3. 数据记录或审核

1.数据验证

假设一些公司有职位空缺，并且已经有了申请数据，条件是：

1. 工作经验必须大于或等于3年，并且
2. 最近2年内不得进行以前的申请尝试。

为了确保数据完整性，我们将创建BEFORE UPDATE触发器，它将限制对违反以上任何条件的数据进行UPDATE。

1.1首先，我们将创建表job_openings。

job_openings

--Creating job_openings table.

CREATE TABLE job_openings
(
    APPLICATION_ID number(10) primary key,
    FIRST_NAME varchar2(50),
    LAST_NAME varchar2(50),
    JOB_EXPERIENCE number(2),
    LAST_APPLIED_DATE date
);

1.2然后，我们将创建一个栏之前更新跳跳虎JOB_EXPERIENCE和LAST_APPLIED_DATE job_openings表。

trg_before_emp_update

-- Creating TRIGGER

CREATE OR REPLACE TRIGGER trg_before_emp_update
BEFORE UPDATE OF JOB_EXPERIENCE,LAST_APPLIED_DATE
  on job_openings
  FOR EACH ROW 

DECLARE

years_since_last_applied number(5);

BEGIN
years_since_last_applied := -1;

  IF(:NEW.LAST_APPLIED_DATE IS NOT NULL) THEN

    SELECT MONTHS_BETWEEN(TO_DATE(sysdate,'DD-MON-YYYY'), TO_DATE(:NEW.LAST_APPLIED_DATE,'DD-MON-YYYY'))/12
      INTO years_since_last_applied FROM dual;

   -- Check whether years_since_last_applied is greater than 2 years or not
    IF (years_since_last_applied <= 2) THEN
      RAISE_APPLICATION_ERROR(-20000,'Previous application attempt must not be done in last 2 years.');
    END IF;
  END IF;
  
    -- Job experience must be more than or equal to 3 years.
    IF(:new.JOB_EXPERIENCE < 3) THEN
      RAISE_APPLICATION_ERROR(-20000,'Job experience must be more than or equal to 3 years.');
    END IF;          
    
END;

1.3正常数据。

-- setting date format to to 'DD-MON-YYYY'

alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO job_openings VALUES (1,'Mark','Sharma',10,'01-JAN-2012');

INSERT INTO job_openings VALUES (2,'Praveen','Kumar',4,'01-DEC-2010');

INSERT INTO job_openings VALUES (3,'Rahul','Kohli',6,null);

-- output
1 rows inserted.
1 rows inserted.
1 rows inserted.

select * from job_openings;

APPLICATION_ID	名字	姓	工作经验	LAST_APPLIED_DATE
1个	标记	夏尔马	10	2012年1月1日
2	普拉文	库玛	4	2010年12月1日
3	拉胡尔	科利	6	空值

1.4测试触发器引发错误–工作经验必须大于或等于3年。

-- Try to update job_experience less than 3 years
UPDATE job_openings SET JOB_EXPERIENCE = 2 where APPLICATION_ID = 1;

-- error 
Error report -
ORA-20000: Job experience must be more than or equal to 3 years.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_UPDATE", line 21
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_UPDATE'

1.5测试触发引发错误–过去2年内不得进行以前的应用尝试。

-- Try to update last_Applied_Date which is less than 2 years
UPDATE job_openings SET LAST_APPLIED_DATE = '10-JUN-2016' where APPLICATION_ID = 3;

-- error
Error report -
ORA-20000: Previous application attempt must not be done in last 2 years.
ORA-06512: at "SYSTEM.TRG_BEFORE_EMP_UPDATE", line 15
ORA-04088: error during execution of trigger 'SYSTEM.TRG_BEFORE_EMP_UPDATE'

2.更新值

一个BEFORE UPDATE触发器示例， :NEW.UPDATED_DATE自动更新:NEW.UPDATED_BY和:NEW.UPDATED_DATE 。

2.1创建一个表。

person_records

--Creating person_records table.

CREATE TABLE person_records
(
    PERSON_ID number(10) primary key,
    FIRST_NAME varchar2(50),
    LAST_NAME varchar2(50),
    HIRE_DATE date,
    UPDATED_BY varchar2(20),
    UPDATED_DATE date
);

2.2在表person_records上创建更新前触发器

trg_before_person_update

CREATE OR REPLACE TRIGGER trg_before_person_update
BEFORE UPDATE
  on person_records
  FOR EACH ROW 

DECLARE
username varchar2(20);

BEGIN

  SELECT USER INTO username FROM dual;
  
  -- Setting updated_by and updated_Date values.
  :NEW.UPDATED_BY := username;
  :NEW.UPDATED_DATE := sysdate;

END;

2.3插入两个记录。

-- setting date format to to 'DD-MON-YYYY'
alter session set nls_date_format = 'DD-MON-YYYY';

INSERT INTO person_records VALUES (101,'Devil','Khedut',sysdate,null,null);
INSERT INTO person_records VALUES (102,'Kanji','Yadav',sysdate,null,null);

-- output
1 rows inserted.
1 rows inserted.

select * from person_records;

PERSON_ID	名字	姓	雇用日期	UPDATED_BY	UPDATED_DATE
101	魔鬼	赫杜特	2017年6月6日	空值	空值
102	汉子	亚达夫	2017年6月6日	空值	空值

2.4更新记录，它将触发更新前触发器。 UPDATED_BY和UPDATED_DATE的值将自动更新。

UPDATE person_records SET first_name = 'Lavji' WHERE person_id = 101;

UPDATE person_records SET first_name = 'Jogi' WHERE person_id = 102;

-- output
1 rows updated.
1 rows updated.

select * from person_records;

PERSON_ID	名字	姓	雇用日期	UPDATED_BY	UPDATED_DATE
101	拉夫吉	赫杜特	2017年6月6日	人力资源	2017年6月6日
102	若木	亚达夫	2017年6月6日	人力资源	2017年6月6日

3.记录/审核数据

在此示例中，我们创建了一个触发器，该触发器将在每次更新事务表之前将行插入到审计表中。 每当用户更新bank_transactions的数据时，触发器都会将旧数据插入到bank_transactions_audit ，以进行审计或备份。

3.1创建银行交易表。

bank_transactions

--Creating bank_transactions table.

CREATE TABLE bank_transactions
(
    TXN_ID number(10) primary key,
    TXN_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    TXN_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

3.2创建另一个银行交易审核表。

bank_transactions_audit

--Creating bank_transactions_audit table.

CREATE TABLE bank_transactions_audit
(
    TXN_ID number(10),
    TXN_NUMBER varchar2(20),
    PARTY_NAME varchar2(50),
    TXN_DATE date,
    CREATED_BY varchar2(20),
    CREATED_DATE date
);

3.3在bank_transactions表上创建更新前触发器。

trg_before_update_txn_audit

--Creating Trigger

CREATE OR REPLACE TRIGGER trg_before_update_txn_audit
BEFORE UPDATE
  ON bank_transactions
  FOR EACH ROW

BEGIN

  -- Insert OLD values in audit table for logging purpose
  INSERT INTO bank_transactions_audit VALUES(:OLD.TXN_ID,:OLD.TXN_NUMBER,
    :OLD.PARTY_NAME,:OLD.TXN_DATE,:OLD.CREATED_BY,:OLD.CREATED_DATE);
  
END;

3.4插入一些记录。

--Inserting values

INSERT INTO BANK_TRANSACTIONS values ('1','TXN1234','Peter Thomas','12-MAY-2017','HR',sysdate);

INSERT INTO BANK_TRANSACTIONS values ('2','TXN9999','Jemes Patel','10-JUN-2016','HR',sysdate);

select * from BANK_TRANSACTIONS;

TXN_ID	TXN_NUMBER	PARTY_NAME	TXN_DATE	由...制作	创建日期
1个	TXN1234	彼得·托马斯	2017年5月12日	人力资源	2017年6月6日
2	TXN9999	杰姆斯·帕特尔	2016年6月10日	人力资源	2017年6月6日

3.5插入操作未触发更新前触发器。

select * from BANK_TRANSACTIONS_AUDIT;

-- output
no rows selected.

3.6更新记录，它将触发“更新前”触发器，并将旧数据插入另一个审核表。

--updating values.

UPDATE bank_transactions SET txn_number = 'NEWTXN8080' WHERE txn_id = 1;

UPDATE bank_transactions SET txn_number = 'NEWTXN9595' WHERE txn_id = 2;

-- output
1 rows updated.
1 rows updated.

select * from BANK_TRANSACTIONS;

TXN_ID	TXN_NUMBER	PARTY_NAME	TXN_DATE	由...制作	创建日期
1个	NEWTXN8080	彼得·托马斯	2017年5月12日	人力资源	2017年6月6日
2	NEWTXN9595	杰姆斯·帕特尔	2016年6月10日	人力资源	2017年6月6日

select * from BANK_TRANSACTIONS_AUDIT;

TXN_ID	TXN_NUMBER	PARTY_NAME	TXN_DATE	由...制作	创建日期
1个	TXN1234	彼得·托马斯	2017年5月12日	人力资源	2017年6月6日
2	TXN9999	杰姆斯·帕特尔	2016年6月10日	人力资源	2017年6月6日

参考文献

1. Oracle – CREATE TRIGGER语句
2. Oracle – PL / SQL触发器

标记： 在更新 oracle plsql 触发器 之前

翻译自: https://mkyong.com/oracle/oracle-plsql-before-update-trigge-example/