《Oracle PL/SQL实例精讲》学习笔记14——触发器-Part1

本章内容：

1. 触发器的基本概念

2. 触发器的类型

 

代码如下：

-- For Example ch13_1a.sql
CREATE OR REPLACE TRIGGER student_bi
BEFORE INSERT ON STUDENT
FOR EACH ROW
BEGIN
   :NEW.student_id    := STUDENT_ID_SEQ.NEXTVAL;
   :NEW.created_by    := USER;
   :NEW.created_date  := SYSDATE;
   :NEW.modified_by   := USER;
   :NEW.modified_date := SYSDATE;
END;

select * from student

desc student_bi

drop trigger student_bi

delete from student where student_id=100

/* Formatted on 2018/11/7 0:20:17 (QP5 v5.256.13226.35538) */
INSERT INTO STUDENT (STUDENT_ID,
                     FIRST_NAME,
                     LAST_NAME,
                     STREET_ADDRESS,
                     ZIP,
                     PHONE,
                     EMPLOYER,
                     REGISTRATION_DATE,
                     CREATED_BY,
                     CREATED_DATE,
                     MODIFIED_BY,
                     MODIFIED_DATE)
     VALUES (100,
             'pd',
             'huang',
             'lianyuan',
             '02189',
             '13917867133',
             'cgroup',
             to_date('2018-11-06','yyyy-mm-dd'),
             'hpd',
             to_date('2018-11-06','yyyy-mm-dd'),
             'huangpd',
             to_date('2018-11-06','yyyy-mm-dd'))

select * from zipcode

select * from student where student_id=100

select * from student order by student_id desc


/* Formatted on 2018/11/7 0:30:11 (QP5 v5.256.13226.35538) */
INSERT INTO STUDENT (FIRST_NAME,
                     LAST_NAME,
                     STREET_ADDRESS,
                     ZIP,
                     PHONE,
                     EMPLOYER,
                     REGISTRATION_DATE)
     VALUES ('pd',
             'huang',
             'lianyuan',
             '02189',
             '13917867133',
             'cgroup',
             TO_DATE ('1980-11-06', 'yyyy-mm-dd'))
             
             
-- For Example ch13_2a.sql

CREATE TABLE audit_trail
(
   TABLE_NAME         VARCHAR2 (100),
   TRANSACTION_NAME   VARCHAR2 (100),
   TRANSACTION_USER   VARCHAR2 (20),
   TRANSACTION_DATE   DATE
)


CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
   v_trans_type VARCHAR2(10); 
BEGIN
   v_trans_type := CASE
                      WHEN UPDATING THEN 'UPDATE'
                      WHEN DELETING THEN 'DELETE'
                    END;
   
   INSERT INTO audit_trail 
      (TABLE_NAME, TRANSACTION_NAME, TRANSACTION_USER, TRANSACTION_DATE)
   VALUES 
      ('INSTRUCTOR', v_trans_type, USER, SYSDATE);
END; 

select * from instructor

update instructor set first_name = 'PdFernand2' where instructor_id=101

select * from audit_trail
/* Formatted on 2018/11/7 0:45:00 (QP5 v5.256.13226.35538) */

-- For Example ch13_2b.sql
CREATE OR REPLACE TRIGGER instructor_aud
AFTER UPDATE OR DELETE ON INSTRUCTOR
DECLARE
   v_trans_type VARCHAR2(10); 
   PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
   v_trans_type := CASE
                      WHEN UPDATING THEN 'UPDATE'
                      WHEN DELETING THEN 'DELETE'
                    END;
   
   INSERT INTO audit_trail 
      (TABLE_NAME, TRANSACTION_NAME, TRANSACTION_USER, TRANSACTION_DATE)
   VALUES 
      ('INSTRUCTOR', v_trans_type, USER, SYSDATE);
   COMMIT;
END;