做资源分享网站,全国做网站排行,房产做网站吸引,企业网站源码生成器一、什么是DDL和数据库事件触发器#xff1f;DDL语句触发DDL触发器#xff1a;CREATE#xff0c;ALTER或DROP。
数据库事件触发器由数据库中的非SQL事件触发#xff0c;例如#xff1a;
•用户连接到数据库或与数据库断开连接。
•DBA启动或关闭数据库。•用户会话中引发了…一、什么是DDL和数据库事件触发器DDL语句触发DDL触发器CREATEALTER或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
2DDL触发器的示例
每次在模式中创建新的数据库对象时都希望写入日志记录
CREATE OR REPLACE TRIGGER log_create_trigg
AFTER CREATE ON SCHEMA
BEGININSERT INTO log_tableVALUES (USER, SYSDATE);
END;只要有任何类型的对象被创建触发器就会触发。 您不能创建引用特定数据库对象的DDL触发器。3DDL触发器的第二个例子防止从模式中删除任何对象。
CREATE OR REPLACE TRIGGER prevent_drop_trigg
BEFORE DROP ON SCHEMA
BEGINRAISE_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示例1LOGON和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;
示例2SERVERERROR触发器想保留会话中发生的任何ORA-00942错误的日志
CREATE OR REPLACE TRIGGER servererror_trig
AFTER SERVERERROR ON SCHEMA
BEGIN
IF (IS_SERVERERROR (942)) THENINSERT 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 EMPLOYEESCALL log_execution四、突变表和行触发器 突变表是一个当前正在由DML语句修改的表。行触发器不能从变异表中选择因为它会看到不一致的数据集当触发器尝试读取数据时表中的数据将会改变。 但是如果需要行触发器可以从不同的表中进行选择。
此限制不适用于DML语句触发器仅适用于DML行触发器。
突变表例子
CREATE OR REPLACE TRIGGER check_salaryBEFORE INSERT OR UPDATE OF salary, job_id ON employeesFOR EACH ROW
DECLAREv_minsalary employees.salary%TYPE;v_maxsalary employees.salary%TYPE;
BEGINSELECT MIN(salary), MAX(salary)INTO v_minsalary, v_maxsalaryFROM employeesWHERE job_id :NEW.job_id;IF :NEW.salary v_minsalary OR:NEW.salary v_maxsalary THENRAISE_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_empBEFORE UPDATE ON employees
BEGIN
IF (TO_CHAR (SYSDATE, DY) IN (SAT,SUN)) THENRAISE_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_salaryBEFORE UPDATE OF salary ON employeesFOR EACH ROWWHEN (NEW.salary OLD.salaryOR NEW.salary 500)
BEGINRAISE_APPLICATION_ERROR (-20508,Do not decrease salary.);
END;3第三个例子
您需要创建一个显示部门总工资单的报表。 你可以声明和使用这个游标
...
CURSOR tot_sals ISSELECT SUM(salary)FROM employeesWHERE department_id p_dept_id;
...但是如果在一个大型组织中该部门有10,000名员工呢 从EMPLOYEES表中抽取10,000行可能太慢。 下面展示了一个更快的方法来做到这一点。首先我们在DEPARTMENTS表中添加一个新列以存储每个部门的总工资单
ALTER TABLE DEPARTMENTS ADD (total_salary NUMBER(12,2));
接下来只填写当前总工资单的这一栏
UPDATE departments dSET total_salary (SELECT SUM(salary) FROM employeesWHERE department_id d.department_id);
现在我们必须在更改工资时保持这一新列。 这是通过使用DML行触发器完成的。
CREATE OR REPLACE PROCEDURE increment_salary(p_id IN NUMBER, p_new_sal IN NUMBER) IS
BEGINUPDATE departmentsSET 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;
