BEFORE INSERT
BEFORE INSERT FOR EACH ROW
AFTER INSERT
AFTER INSERT FOR EACH ROW
 
BEFORE UPDATE
BEFORE UPDATE FOR EACH ROW
AFTER UPDATE
AFTER UPDATE FOR EACH ROW
 
BEFORE DELETE
BEFORE DELETE FOR EACH ROW
AFTER DELETE
AFTER DELETE FOR EACH ROW

--DML触发器基本定义：
　　CREATE  [OR REPLACE] TRIGGER  [user.] trigger
　　{BEFORE|AFTER}  {INSERT | DELETE | UPDATE [OF column [, column …]]}
　　[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...] on
 [user.]table
　　[FOR EACH ROW]
　　[WHEN (condition)]
　　BEGIN
　　　　.................
　　END;

--准备操作
INSERT INTO DEPT(DEPTNO,DNAME,LOC) VALUES(60,\'研发部\',\'上海\');
/
INSERT INTO EMP(EMPNO, ENAME,JOB,
                MGR,HIREDATE,SAL,
                COMM,DEPTNO)
              VALUES(1001,\'贾宝玉\',\'程序员\',7788,TO_DATE (\'2013-02-03\',\'YYYY-MM-DD\'),8000,1000,60);
/             
CREATE VIEW emp_info_view
 AS 
 SELECT E.EMPNO,E.ENAME,E.JOB,E.SAL,D.DNAME,D.LOC
 FROM EMP e JOIN DEPT d ON e.deptno=d.deptno ;
 /
 SELECT * FROM emp_info_view;
 
--创建替代触发器
create or replace trigger emp_info_view_trigger 
instead of update  
    on emp_info_view for each row  
 begin  
  update dept set dname=:new.dname,loc=:new.loc
  where dname=:new.dname;  
 end; 
--更新视图
/
update emp_info_view set loc=\'杭州\' where dname=\'研发部\'
--查询结果
/
SELECT * FROM dept；

CREATE OR REPLACE VIEW emp_view AS 
SELECT deptno, count(*) total_employeer, sum(sal) total_salary 
FROM emp GROUP BY deptno;
/
在此视图中直接删除是非法：
SQL>DELETE FROM emp_view WHERE deptno=10;
DELETE FROM emp_view WHERE deptno=10
*
ERROR 位于第 1 行:
ORA-01732: 此视图的数据操纵操作非法

--但是我们可以创建INSTEAD_OF触发器来为 DELETE 操作执行所需的处理，即删除EMP表中所有基准行：
/
CREATE OR REPLACE TRIGGER emp_view_delete
INSTEAD OF DELETE ON emp_view FOR EACH ROW
BEGIN
DELETE FROM emp WHERE deptno= :old.deptno;
END emp_view_delete;
/
DELETE FROM emp_view WHERE deptno=10;

CREATE [OR REPLACE] TRIGGER trigger name                         --创建一个触发器并制定名称，or replace是可选项 
 {BEFORE | AFTER } { DDL event} ON {DATABASE | SCHEMA}     --指定触发器是在DDL事件之前、之后触发。范围是on database、on schema 
 [WHEN (...)]                                                                               --可选的WHEN子句，使用逻辑判断来避免触发器无意义的执行 
 DECLARE                                                                                  --触发器具体内容4-7
 Variable declarations 
BEGIN 
 ...some code... 
END;
Examples:
SQL> CREATE OR REPLACE TRIGGER hr.testtrigger 
 AFTER CREATE ON SCHEMA  -- on schema 作用范围只是在hr用户下create table等触发，其他用户则不会。若是on database则其他用户create table时会触发该触发器 
 BEGIN 
   -- 以下使用的是事件属性 
 DBMS_OUTPUT.PUT_LINE(\'I believe you have created a \' || 
 ORA_DICT_OBJ_TYPE || \' called \' || 
 ORA_DICT_OBJ_NAME); 
 END; 
 / 
Trigger created.

CREATE OR REPLACE TRIGGER HR.no_drop 
BEFORE DDL ON DATABASE 
BEGIN 
IF ORA_SYSEVENT = \'CREATE\' 
THEN 
DBMS_OUTPUT.PUT_LINE(\'Warning !!! You have created a \'|| 
                        ORA_DICT_OBJ_TYPE ||\' called \'|| 
                        ORA_DICT_OBJ_NAME|| \'; UserName(creater):\'|| 
                        ORA_DICT_OBJ_OWNER||\'; IP:\'|| 
                        ORA_CLIENT_IP_ADDRESS||\'; event:\'|| 
                        ORA_SYSEVENT);                        
ELSIF ORA_SYSEVENT = \'DROP\' 
THEN 
RAISE_APPLICATION_ERROR (-20000, 
\'Cannot create the \' || ORA_DICT_OBJ_TYPE || 
\' named \' || ORA_DICT_OBJ_NAME || 
\' as requested by \' || ORA_DICT_OBJ_OWNER); 
END IF; 
END;

CREATE OR REPLACE TRIGGER preserve_app_cols 
   AFTER ALTER ON SCHEMA 
DECLARE 
   -- cursor to get columns in a table 
   CURSOR curs_get_columns (cp_owner VARCHAR2, cp_table VARCHAR2) 
   IS 
      SELECT column_name 
        FROM all_tab_columns 
       WHERE owner = cp_owner AND table_name = cp_table; 
BEGIN 
   -- if it was a table that was altered... 
   IF ora_dict_obj_type = \'TABLE\' 
   THEN 
      -- for every column in the table... 
      FOR v_column_rec IN curs_get_columns ( 
                             ora_dict_obj_owner, 
                             ora_dict_obj_name 
                          ) 
      LOOP 
         -- if the current column was the one that was altered then say so 
         IF ora_is_alter_column (v_column_rec.column_name) 
         THEN 
            -- if the table/column is core? 
            IF is_application_column ( 
                  ora_dict_obj_owner, 
                  ora_dict_obj_name, 
                  v_column_rec.column_name 
               ) 
            THEN 
               RAISE_APPLICATION_ERROR ( 
                  -20001, 
                  \'Cannot alter core application attributes\' 
               ); 
            END IF; -- table/column is core 
         END IF; -- current column was altered 
      END LOOP; -- every column in the table 
   END IF; -- table was altered 
END; 

CREATE OR REPLACE TRIGGER hr.what_privs 
   AFTER GRANT ON SCHEMA 
DECLARE 
   v_grant_type     VARCHAR2 (30); 
   v_num_grantees   BINARY_INTEGER; 
   v_grantee_list   ora_name_list_t; 
   v_num_privs      BINARY_INTEGER; 
   v_priv_list      ora_name_list_t; 
BEGIN 
   v_grant_type := ora_dict_obj_type; 
   v_num_grantees := ora_grantee (v_grantee_list); 
   v_num_privs := ora_privilege_list (v_priv_list);
   IF v_grant_type = \'ROLE PRIVILEGE\' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || \'The following roles/privileges were granted\' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || \'Privilege \' || v_priv_list (counter) 
         ); 
      END LOOP; 
   ELSIF v_grant_type = \'OBJECT PRIVILEGE\' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || \'The following object privileges were granted\' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || \'Privilege \' || v_priv_list (counter) 
         ); 
      END LOOP;
      DBMS_OUTPUT.put (CHR (9) || \'On \' || ora_dict_obj_name);
      IF ora_with_grant_option 
      THEN 
         DBMS_OUTPUT.put_line (\' with grant option\'); 
      ELSE 
         DBMS_OUTPUT.put_line (\'\'); 
      END IF; 
   ELSIF v_grant_type = \'SYSTEM PRIVILEGE\' 
   THEN 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || \'The following system privileges were granted\' 
      );
      FOR counter IN 1 .. v_num_privs 
      LOOP 
         DBMS_OUTPUT.put_line ( 
            CHR (9) || CHR (9) || \'Privilege \' || v_priv_list (counter) 
         ); 
      END LOOP; 
   ELSE 
      DBMS_OUTPUT.put_line (\'I have no idea what was granted\'); 
   END IF;
   FOR counter IN 1 .. v_num_grantees 
   LOOP 
      DBMS_OUTPUT.put_line ( 
         CHR (9) || \'Grant Recipient \' || v_grantee_list (counter) 
      ); 
   END LOOP; 
END; 

CREATE TABLE log_event
(user_name VARCHAR2(10),
 address VARCHAR2(20), 
 logon_date timestamp,
 logoff_date timestamp); 

--创建登录触发器
CREATE OR REPLACE TRIGGER tr_logon
AFTER LOGON ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logon_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logon;
--创建退出触发器
CREATE OR REPLACE TRIGGER tr_logoff
BEFORE LOGOFF ON DATABASE
BEGIN
   INSERT INTO log_event (user_name, address, logoff_date)
   VALUES (ora_login_user, ora_client_ip_address, systimestamp);
END tr_logoff;