查看: 1646|回复: 5
OGG排除触发器DDL
认证徽章论坛徽章:0
源端 oracle9i + aix
目标 oracle11g + windows
ogg是单向复制，开启了DDL操作的复制，当源端建立一个触发器的时候，目标端也会随之建立，并且启用状态，请问如何能避免这个问题？
源端建立触发器的DDL操作，不要同步到目标端。谢谢。
论坛徽章:6
DDL同步参数选项那里不选择触发器应该就可以
认证徽章论坛徽章:0
ftc007 发表于
DDL同步参数选项那里不选择触发器应该就可以
能说的具体点嘛&&大神& & 给出具体的排除参数呗& &我ogg小白。
认证徽章论坛徽章:0
ftc007 发表于
DDL同步参数选项那里不选择触发器应该就可以
能说的具体点嘛&&大神& & 给出具体的排除参数呗& &我ogg小白。
认证徽章论坛徽章:0
ftc007 发表于
DDL同步参数选项那里不选择触发器应该就可以
能说的具体点嘛&&大神& & 给出具体的排除参数呗& &我ogg小白。
论坛徽章:6
加我QQ，我帮你看看。
itpub.net All Right Reserved. 北京盛拓优讯信息技术有限公司版权所有　　　　
　北京市公安局海淀分局网监中心备案编号：10　广播电视节目制作经营许可证：编号（京）字第1149号出于安全性或避免影响性能的考虑，在产品数据库中有时候会禁止或者在一定时间段内限制DDL语句的发生。Oracle也提高了很多方法来实现这个功能，这个简单介绍一下。
这篇介绍利用触发器来限制DDL语句。
我们用3个用户举例，test 用户不受触发器限制，test2用户和test3 用户受触发器限制，不允许DDL操作
SQL& create user
User created.
SQL& create user
test2 identified by test2;
User created.
SQL& create user
test3 identified by test3;
User created.
SQL& grant
connect,resource to test,test2,test3;
Grant succeeded.
分别在3个用户下创建一个表
SYS@test&conn
Connected.
TEST@test&create
table t (id int);
Table created.
TEST@test&conn
test2/test2
Connected.
TEST2@test&create
table t (id int);
Table created.
TEST2@test&conn
test3/test3
Connected.
TEST3@test&create
table t (id int);
Table created.
试下看看能不能做ddl操作
TEST3@test&conn
Connected.
TEST@test&alter
table t add (name int);
Table altered.
TEST@test&conn
test2/test2
Connected.
TEST2@test&alter
table t add (name int);
Table altered.
TEST2@test&conn
test3/test3
Connected.
TEST3@test&alter
table t add (name int);
Table altered.
由此看出3个用户目前都能做DDL操作
下面创建触发器。使当test做DDL操作时候数据不报错。test2，test3做DDL操作数据库报错，不允许做ddl操作
创建触发器
TEST3@test&conn /
Connected.
SYS@test&CREATE
or& replace TRIGGER ddl_trigger
before ddl on database
ora_dict_obj_owner() = 'TEST2'& then
raise_application_error(-20001,'You can not execute ddl& on&&&
'|| ora_dict_obj_name );
elsif ora_dict_obj_owner() = 'TEST3' then
raise_application_error(-20001,'You can not execute ddl& on&&&
'|| ora_dict_obj_name );
Trigger created.
下面分别在3个用户做测试。
SYS@test&conn
Connected.
TEST@test&alter
table t add (com int);
Table altered.
在test用户下没有问题
在test2.test3，用户下数据库都报错，禁止DDL操作
TEST@test&conn
test2/test2
Connected.
TEST2@test&alter
table t add (com int);
alter table t add
ERROR at line 1:
ORA-00604: error
occurred at recursive SQL level 1
ORA-20001: You can
not execute ddl& on&&& T
ORA-06512: at line 6
TEST2@test&conn
test3/test3
Connected.
TEST3@test&alter
table t add (com int);
alter table t add
ERROR at line 1:
ORA-00604: error
occurred at recursive SQL level 1
ORA-20001: You can
not execute ddl& on&&& T
ORA-06512: at line 8
以下是触发器全部内容，供大家参考
CREATE or& replace TRIGGER ddl_trigger
before ddl on
ora_dict_obj_owner() = 'TEST2'& then
raise_application_error(-20001,'You
can not execute ddl& on&&& '|| ora_dict_obj_name );
ora_dict_obj_owner() = 'TEST3'
raise_application_error(-20001,'You
can not execute ddl& on&&& '|| ora_dict_obj_name );
&&&回复&&&：
北京盛拓优讯信息技术有限公司. 版权所有 京ICP备号 北京市公安局海淀分局网监中心备案编号：10
广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员191 次阅读
标签：至少1个，最多5个
上篇文章简单介绍了一下MySQL的基本操作之DDL、DML、DQL、DCL，在DDL中简单提了一下触发器，存储过程和函数，本篇文章将详细介绍触发器！
1、触发器作用：简单来说，触发器就是绑定在某个表上的一个特定数据库对象，当在这个表上发生某种触发器所监听的操作时，将会触发某种动作。
2、触发器用法：
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW
...trigger_statement... #触发器的逻辑实现
参数解释：
trigger_name:触发器的名称
trigger_event:触发的事件，包括：INSERT，UPDATE，DELETE
trigger_time:触发的时间点，包括：BEFORE（事件之前触发），AFTER（事件之后触发）
table_name:触发器所在表
trigger_statement:触发器被触发之后，所执行的数据库操作逻辑，可以为单一的数据库操作，或者一系列数据库操作集合，也可以包含一些判断等处理逻辑；
注意：（1）同一张表中不能同时存在两个类型一样的触发器；
（2）触发事件和触发时间点总共可以组成3组6种不同的触发器，分别为：（BEFORE INSERT，AFTER INSERT）、（BEFORE UPDATE，AFTER UPDATE）、（BEFORE DELETE，AFTER DELETE）；
（3）触发事件：① INSERT：在插入数据的时候触发，插入数据的动作包括INSERT，LOAD DATA，REPLACE操作，即：发生这三种操作时，都会触发INSERT类型的触发器；② UPDATE：数据发生变更时触发，即：发生了UPDATE操作；③ DELETE：从表中删除某一行的时候触发，即：发生了DELETE或者REPLACE操作；
（4）创建触发器的时候，由于在触发器的trigger_statement语句中有逻辑，而每个逻辑都会有结束符，默认为";"，故需要在创建之前先定义定界符。防止SQL语句在执行之前被存储引擎（存储引擎:MySQL数据库的插件，后续介绍）解析的时候碰见";"而提前结束，提示语法错误。
（1）示例1：现在test_db中有两个表，一个为员工信息表t_emp，一个为部门统计表t_dept_statis，他们的表结构分别如下所示：
员工信息表：
CREATE TABLE `t_emp` (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL DEFAULT '', #员工姓名
`age` TINYINT(4) DEFAULT NULL,
`gender` ENUM('F','M') DEFAULT NULL,
`dept_id` INT,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARSET=UTF8;
部门员工数量统计表：
CREATE TABLE t_dept_statis(
id INT PRIMARY KEY AUTO_INCREMENT,
emp_count INT, #员工数量，初始化为0
dept_id INT,
update_time DATETIME #更新时间
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
初始化的统计数据，插入两条统计数据，分别为编号为1和2的两个部门，初始化员工数量为0：
mysql& INSERT INTO t_dept_statis(emp_count,dept_id,update_time) VALUES(0,1,NOW());
mysql& INSERT INTO t_dept_statis(emp_count,dept_id,update_time) VALUES(0,2,NOW());
需求：使用触发器实现每新增一条员工记录，部门信息统计表中就可以自动统计出员工数有变化的部门的员工总数量。这个需求可能不合适，但是完全可以说明触发器的用法：
mysql& \d $
#建立定界符，可以使用"DELIMITER $"，和"\d $"等价
mysql& CREATE TRIGGER dep_tri AFTER INSERT ON t_emp FOR EACH ROW
DECLARE num INT;
SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_
#重新还原定界符为默认的定界符";"
#查看t_emp中的数据，此时是空的
mysql& SELECT * FROM t_
Empty set (0.00 sec)
查看t_dept_statis中的数据，此时有两条初始化数据，员工数量为0
mysql& SELECT * FROM t_dept_
+----+-----------+---------+-------------+
| id | emp_count | dept_id | update_time |
+----+-----------+---------+-------------+
+----+-----------+---------+-------------+
向t_emp中插入一条数据，然后查看t_dept_statis表，会发现，员工数量会自动统计
mysql& INSERT INTO t_emp(name,age,gender,dept_id) values('emp01',23,'F',1);
Query OK, 1 row affected (0.00 sec)
mysql& SELECT * FROM t_dept_
+----+-----------+---------+---------------------+
| id | emp_count | dept_id | update_time
+----+-----------+---------+---------------------+
22:51:15 |
+----+-----------+---------+---------------------+
再次向t_emp中插入一条数据，然后查看t_dept_statis表，会发现，员工数量会再次统计
mysql& INSERT INTO t_emp(name,age,gender,dept_id) values('emp03',26,'M',2);
Query OK, 1 row affected (0.15 sec)
mysql& SELECT * FROM t_dept_
+----+-----------+---------+---------------------+
| id | emp_count | dept_id | update_time
+----+-----------+---------+---------------------+
22:51:15 |
22:51:30 |
+----+-----------+---------+---------------------+
查看t_emp中的数据，会发现目前有两条记录，部门1和部门二中各有一条，统计表已经通过触发器实现了员工数量的自动统计：
mysql& SELECT * FROM t_
+----+-------+------+--------+---------+
| id | name
| gender | dept_id |
+----+-------+------+--------+---------+
1 | emp01 |
2 | emp03 |
+----+-------+------+--------+---------+
2 rows in set (0.00 sec)
（2）示例2：在test_db中有一张用户表t_user和t_user_bak，表结构相同，如下所示：
mysql& CREATE TABLE t_user(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '', #用户名
age TINYINT NOT NULL DEFAULT 0,
create_time DATETIME NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
mysql& CREATE TABLE t_user_bak(id INT PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(50) NOT NULL DEFAULT '', #用户名
age TINYINT NOT NULL DEFAULT 0,
create_time DATETIME NOT NULL
) ENGINE = InnoDB DEFAULT CHARSET = UTF8;
创建测试数据，插入如下几条数据：
mysql& INSERT INTO t_user(name,age,create_time) VALUES('name01',23,NOW());
mysql& INSERT INTO t_user(name,age,create_time) VALUES('name02',25,NOW());
需求：如果t_user表中的数据被修改，则将修改前的数据先备份到t_user_bak表中，使用触发器实现：
mysql& \d $
mysql& CREATE TRIGGER user_bak_tri BEFORE UPDATE ON t_user FOR EACH ROW
INSERT INTO t_user_bak(name,age,create_time) VALUES(old.name,old.age,NOW());
查询t_user_bak表中的数据，此时为空：
mysql& SELECT * FROM t_user_
Empty set (0.00 sec)
修改t_user表中id为1的数据，然后再次查看t_user_bak表中的数据：
mysql& UPDATE t_user SET name = 'name001' WHERE name = 'name01';
mysql& SELECT * FROM t_user_
+----+--------+-----+---------------------+
| id | name
| age | create_time
+----+--------+-----+---------------------+
1 | name01 |
05:07:40 |
+----+--------+-----+---------------------+
1 row in set (0.00 sec)
可见，数据已经自动备份到t_user_bak中。
4、触发器中的new和old关键字：
（1）作用：用来访问受触发器影响的行中的列（2）用法：
a、在INSERT操作中，new表示将要插入（BEFORE INSERT）或者已经插入（AFTER INSERT）表中的数据；
b、在UPDATE操作中，new表示将要插入或者已经插入的新数据，而old表示将要插入或者已经插入的原数据；
c、在DELETE操作中，old表示将要删除或者已经被删除的原数据；
d、OLD是只读的，而NEW则可以在触发器中使用SET赋值，这样不会再次触发触发器，造成循环调用；
5、触发器管理：
（1）查看已经创建好的触发器：语法：
mysql& USE db_
#选择数据库
mysql& SHOW TRIGGERS;
#查看选择的数据库中已经创建的所有触发器
mysql& SHOW CREATE TRIGGER trigger_ #查看某个触发器的创建过程
示例：查看test_db库中已经创建好的所有触发器：
mysql& USE test_
#选择数据库
mysql& SHOW TRIGGERS \G
#查看该库中的触发器
*************************** 1. row ***************************
Trigger: dep_tri
Event: INSERT
Table: t_emp
Statement: BEGIN
DECLARE num INT;
SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_
Timing: AFTER
Created: NULL
sql_mode: STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION
Definer: root@127.0.0.1
character_set_client: utf8
collation_connection: utf8_general_ci
Database Collation: utf8_general_ci
2 rows in set (0.13 sec)
参数解释：
Trigger：触发器名称
Event：触发器所绑定的事件，即：发生什么操作时会执行触发器程序
Table：触发器所在的表
Statement：触发器的逻辑
Timing：触发器的事件
Created：表示创建时间
sql_mode：sql模式，STRICT_TRANS_TABLES表示当一个数据不能插入到一个事务表中，则中断当前操作，NO_ENGINE_SUBSTITUTION表示编译的时候如果没有选择默认存储引擎，则会使用一个默认的存储引擎，并提示一个错误；
Definer：创建触发器的用户
character_set_client：客户端使用的字符集
collation_connection：连接数据库使用的字符校验集
Database Collation：数据库使用的字符校验集
除此之外，还可以使用information_schema库中的trigger表查看已经存在的触发器，如下：
mysql& USE information_
mysql& SELECT TRIGGER_SCHEMA AS 'db_name',EVENT_OBJECT_TABLE as 'table_name',TRIGGER_NAME as 'trigger_name',ACTION_STATEMENT AS 'trigger_statement' FROM TRIGGERS \G
*************************** 1. row ***************************
db_name: test_db
table_name: t_emp
trigger_name: dep_tri
trigger_statement: BEGIN
DECLARE num INT;
SET num = (SELECT emp_count FROM t_dept_statis WHERE dept_id = new.dept_id);
UPDATE t_dept_statis SET emp_count = num + 1 ,dept_id = new.dept_id, update_time = now() where dept_id = new.dept_
2 rows in set (0.02 sec)
（2）删除指定的触发器：语法：
mysql& DROP TRIGGER trigger_
示例：删除t_emp表上的dep_tri索引：
mysql& DROP TRIGGER dep_
6、触发器的优缺点：
优点：可以方便而且高效的维护数据；
缺点：a、高并发场景下容易导致死锁，拖死数据库，成为数据库瓶颈，故高并发场景下一定要慎用；b、触发器比较多的时候不容易迁移，而且表之间数据导入和导出可能会导致无意中触发某个触发器，造成数据错误，故对于数据量比较大，而且数据库模型非常复杂的情况下慎用；
7、事务场景下的注意要点：
MySQL中使用了插件式的存储引擎（存储引擎后文会详细介绍），对于InnoDB事务型的存储引擎，如果SQL语句执行错误，或者触发器执行错误，会发生什么结果呢？（1）如果触发器或者SQL语句执行过程中出现错误，则会发生事务的回滚；（2）SQL语句如果执行失败，则AFTER类型的触发器不会执行；（3）如果AFTER类型的触发器执行失败，则触发此触发器的SQL语句将会回滚；（4）如果BEFORE类型的触发器执行失败，则触发此触发程序的SQL语句将会执行失败；
至此，触发器相关内容介绍完毕，下一个章节介绍存储过程和函数，欢迎转发，讨论，共同学习~
0 收藏&&|&&0
分享到微博？
我要该，理由是：
在 SegmentFault，学习技能、解决问题
每个月，我们帮助 1000 万的开发者解决各种各样的技术问题。并助力他们在技术能力、职业生涯、影响力上获得提升。 上传我的文档
 上传文档
 下载
 收藏
该文档贡献者很忙，什么也没留下。
 下载此文档
SQL Server 触发器的操作与管理
下载积分：100
内容提示：SQL Server 触发器的操作与管理
文档格式：PPT|
浏览次数：2|
上传日期： 22:16:09|
文档星级：
全文阅读已结束，如果下载本文需要使用
 100 积分
下载此文档
该用户还上传了这些文档
SQL Server 触发器的操作与管理
关注微信公众号追求理性之美,简单之美
Oracle触发器(trigger):view,schema,database
视图trigger, instead of
我们知道如果一个view只是由一个table构成,那在view上做啥操作没太多限制.如果view是由多个table组成那在view上做啥unpdate,insert,delete都会出错.但有时又确实要做这些操作该咋办呢.这就需要用到trigger,然后通过instead of关键字来指定一些替代操作.
举个简单例子,如果有view, my_view创建trigger如下
create or replace trigger my_view_trigger
instead of insert or update
on my_view
insert into tmp(eno) values(:new.eno);
当执行sql : insert into my_view(eno, name) values(88,'test');时触发trigger.
不过view的instead of类型的trigger相对其他类型trigger有个特别的地方.从名字也可以看出来,可以替换掉了触发它的sql的操作.也就是insert into my_view(eno, name) values(88,'test');这个sql本身的操作不会起作用了.只有trigger里面的pl/sql语句块才真正执行.
1.instead of 类型触发器只能针对view创建,并且该view上不能有些check option(比如with check read only之类的),这以所这样是防止不同的功能之间的冲突.假如是一个read only类型的view,那自然不能整出个trigger又可以做些DML操作了.
2.不能指定before或after选项,因为触发trigger的sql实际上并不会执行,所以before或after就没有啥意义了.
Database , Schema级别trigger
针对表和视图的Triggers可能开发人员用的多.针对database,schema的Trigger一般是DBA用的多点.
比如创建trigger每当schema上有DDL操作时触发(针对表或视图的trigger只能针对DML操作,不能针对DDL操作).
举个简单的例子
CREATE OR REPLACE TRIGGER ddl_trigger
AFTER DDL ON SCHEMA
insert into tblog values(systimestamp,ora_sysevent, ora_login_user,
ora_dict_obj_type, ora_dict_obj_name);
其中ora_login_user是登陆名,ora_dict_obj_type对象类型(比如表或视图),ora_dict_obj_name是对象名字,比如表名或视图名.你可能看到这些变量貌似没在哪里定义.实际上是oracle定义好的，你只要拿来用就行.
假如随便用哪个用户执行如下sql: create table tmp_tb(eno int);
就会触发trigger.
不过貌似上面不能直接指定是具体的哪个schema,只能针对所有schema了啊.
假如用户每次登陆时要做些记录.那可以创建如下trigger
CREATE OR REPLACE TRIGGER
logon_trigger
AFTER LOGON ON DATABASE
INSERT INTO tblog VALUES (ora_login_user, ora_client_ip_address, systimestamp);
这里的logon on database不是说数据库启动,而是每次连接一个session的时候.
没有更多推荐了，}