一、什么是DDL和数据库事件触发器?
DDL语句触发DDL触发器:CREATE,ALTER或DROP。数据库事件触发器由数据库中的非SQL事件触发,例如:
•用户连接到数据库或与数据库断开连接。
•DBA启动或关闭数据库。
•用户会话中引发了特定的异常。
(1)在DDL语句中创建触发器语法
•ON DATABASE在数据库中的所有模式上触发DDL•ON SCHEMA仅针对您自己的模式中的对象触发DDL
CREATE [OR REPLACE] TRIGGER trigger_name Timing [ddl_event1 [OR ddl_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body
(2)DDL触发器的示例
每次在模式中创建新的数据库对象时,都希望写入日志记录:
CREATE OR REPLACE TRIGGER log_create_trigg AFTER CREATE ON SCHEMA BEGIN INSERT INTO log_table VALUES (USER, SYSDATE); END;只要有任何(类型)的对象被创建,触发器就会触发。 您不能创建引用特定数据库对象的DDL触发器。
(3)DDL触发器的第二个例子
防止从模式中删除任何对象。
CREATE OR REPLACE TRIGGER prevent_drop_trigg BEFORE DROP ON SCHEMA BEGIN RAISE_APPLICATION_ERROR (-20203, 'Attempted drop – failed'); END;
只要有任何(类型)的对象被删除,触发器就会触发。 同样,您不能创建引用特定数据库对象的DDL触发器。
(4)在数据库事件语法上创建触发器
•ON DATABASE触发数据库中所有会话的事件触发器。•ON SCHEMA仅为您自己的会话触发触发器。
CREATE [OR REPLACE] TRIGGER trigger_name timing [database_event1 [OR database_event2 OR ...]] ON {DATABASE|SCHEMA} trigger_body
二、LOGON、LOGOFF和SERVERERROR
示例1:LOGON和LOGOFF触发器
CREATE OR REPLACE TRIGGER logon_trig AFTER LOGON ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging on'); END;
CREATE OR REPLACE TRIGGER logoff_trig BEFORE LOGOFF ON SCHEMA BEGIN INSERT INTO log_trig_table(user_id,log_date,action) VALUES (USER, SYSDATE, 'Logging off'); END;
示例2:SERVERERROR触发器
想保留会话中发生的任何ORA-00942错误的日志:
CREATE OR REPLACE TRIGGER servererror_trig AFTER SERVERERROR ON SCHEMA BEGIN IF (IS_SERVERERROR (942)) THEN INSERT INTO error_log_table ... END IF; END;
三、触发器中的CALL语句
没有结束;语句,并且在CALL语句结尾处没有分号。
语法:
CREATE [OR REPLACE] TRIGGER trigger_name timing event1 [OR event2 OR event3] ON table_name [REFERENCING OLD AS old | NEW AS new] [FOR EACH ROW] [WHEN condition] CALL procedure_name
例子:
CREATE OR REPLACE TRIGGER log_employee BEFORE INSERT ON EMPLOYEES CALL log_execution
四、突变表和行触发器
突变表是一个当前正在由DML语句修改的表。行触发器不能从变异表中选择,因为它会看到不一致的数据集(当触发器尝试读取数据时,表中的数据将会改变)。 但是,如果需要,行触发器可以从不同的表中进行选择。
此限制不适用于DML语句触发器,仅适用于DML行触发器。
突变表:例子
CREATE OR REPLACE TRIGGER check_salary BEFORE INSERT OR UPDATE OF salary, job_id ON employees FOR EACH ROW DECLARE v_minsalary employees.salary%TYPE; v_maxsalary employees.salary%TYPE; BEGIN SELECT MIN(salary), MAX(salary) INTO v_minsalary, v_maxsalary FROM employees WHERE job_id = :NEW.job_id; IF :NEW.salary < v_minsalary OR :NEW.salary > v_maxsalary THEN RAISE_APPLICATION_ERROR(-20505,'Out of range'); END IF; END;
UPDATE employees SET salary = 3400 WHERE last_name = 'Davies';出错:
ORA-04091: table USVA_TEST_SQL01_T01_EMPLOYEES is mutating, trigger/function may not see it ORA-06512: at “USVA_TEST_SQL01_T01.CHECK_SALARY”, line 5 ORA-04088: error during execution of trigger ‘USVA_TEST_SQL01_T01.CHECK_SALARY’ 3. WHERE last_name – ‘Davies’;
五、触发器的更多可能用途
不应该创建触发器来执行某些可以通过其他方式轻松完成的操作,例如通过检查约束或适当的对象权限。 但是有时你必须创建一个触发器,因为没有其他方法可以做需要的事情。以下示例只显示了必须创建触发器的三种情况。 还有更多!
(1)第一个例子
数据库安全性(谁可以做什么)通常由系统和对象权限控制。 例如,用户SCOTT需要更新EMPLOYEES行:
GRANT UPDATE ON employees TO scott;
但是,SCOTT被允许这样做时,单凭权限无法控制。 为此,我们需要一个触发器:
CREATE OR REPLACE TRIGGER weekdays_emp BEFORE UPDATE ON employees BEGIN IF (TO_CHAR (SYSDATE, 'DY') IN ('SAT','SUN')) THEN RAISE_APPLICATION_ERROR(-20506,'You may only change data during normal business hours.'); END IF; END;
(2)第二个例子
数据库完整性(允许DML)通常由约束条件控制。 例如,每个员工的工资必须至少为500美元:
ALTER TABLE employees ADD CONSTRAINT ck_salary CHECK (salary >= 500);
如果一条业务规则指出员工的薪水可以提高但不降低,这个限制并不能阻止员工的薪水从700美元降低到600美元。 为此,我们需要一个行触发器。此代码显示在下一张幻灯片中。
现在我们不再需要约束了。
CREATE OR REPLACE TRIGGER check_salary BEFORE UPDATE OF salary ON employees FOR EACH ROW WHEN (NEW.salary < OLD.salary OR NEW.salary < 500) BEGIN RAISE_APPLICATION_ERROR (-20508, 'Do not decrease salary.'); END;
(3)第三个例子
您需要创建一个显示部门总工资单的报表。 你可以声明和使用这个游标:
... CURSOR tot_sals IS SELECT SUM(salary) FROM employees WHERE department_id = p_dept_id; ...但是,如果在一个大型组织中,该部门有10,000名员工呢? 从EMPLOYEES表中抽取10,000行可能太慢。 下面展示了一个更快的方法来做到这一点。
首先,我们在DEPARTMENTS表中添加一个新列以存储每个部门的总工资单:
ALTER TABLE DEPARTMENTS ADD (total_salary NUMBER(12,2));
接下来,只填写当前总工资单的这一栏:
UPDATE departments d SET total_salary = (SELECT SUM(salary) FROM employees WHERE department_id = d.department_id);
现在,我们必须在更改工资时保持这一新列。 这是通过使用DML行触发器完成的。
CREATE OR REPLACE PROCEDURE increment_salary (p_id IN NUMBER, p_new_sal IN NUMBER) IS BEGIN UPDATE departments SET total_salary = total_salary + NVL(p_new_sal,0) WHERE department_id = p_id; END increment_salary;
CREATE OR REPLACE TRIGGER compute_salary AFTER INSERT OR UPDATE OF salary OR DELETE ON employees FOR EACH ROW BEGIN IF DELETING THEN increment_salary (:OLD.department_id,(:OLD.salary * -1)); ELSIF UPDATING THEN increment_salary (:NEW.department_id,(:NEW.salary - :OLD.salary)); ELSE increment_salary (:NEW.department_id,:NEW.salary); END IF; END;