如题。
触发器存在,只是禁用。
类似alter table 表名 disable trigger 触发器名称13.1.2. ALTER TABLE语法
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| partition_options
| ADD PARTITION partition_definition
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
MYSQL语法里没有。
或许有其他某些变通的方式可以实现。
感谢各位了。
触发器存在,只是禁用。
类似alter table 表名 disable trigger 触发器名称13.1.2. ALTER TABLE语法
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| partition_options
| ADD PARTITION partition_definition
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
MYSQL语法里没有。
或许有其他某些变通的方式可以实现。
感谢各位了。
then
这里就是不写什么..
end if;
else
你触发器真正要做的事情然后当你想禁用这个触发器的时候
你就家里那个XXX的表 然后执行你的语句
这样触发器探测到这个xxx表存在 就啥都不做了。。
CREATE TABLE test (iss int);
delimiter $$
CREATE TRIGGER trIn
AFTER INSERT ON test
FOR each ROW
BEGIN
IF (select count(*) from information_schema.tables where table_name='xxx')=0
THEN
SET @n=1; ----你要做的操作
ELSE
SET @n=2; ----这里你可以随意写个东西 反正就是不触发本来的操作
END IF ;
END;
$$delimiter ;
--testmysql> set @N=0;
Query OK, 0 rows affected (0.00 sec)mysql> create table xxx(a int);
Query OK, 0 rows affected (0.01 sec)mysql> insert test select 1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0mysql> select @n;
+------+
| @n |
+------+
| 2 |
+------+
1 row in set (0.00 sec)
mysql> create trigger tr_1 after insert on a for each row
-> begin
-> insert into b select new.id;
-> delete from b where id =new.id;
-> end;
->
不知这样算不算变通的办法?
事情的原因是这样的。
A主库,B从库,由于业务关系,B库上的某些表新建了一些触发器,但在A库上,是不需要这些触发器的。现在我想把A,B统一成一个标准库,好用来做发布。这样就必须用B库做标准库了,因为B的库结构要比A完整。但是怎么样使A上的那些触发器不执行呢?删除掉是个办法。
索引是可以 disable的
禁用: alter table 表名 disable trigger 触发器名
启用: alter table 表名 enable trigger 触发器名 --禁用某个表的所有触发器
alter table 表名 disable trigger all
--启用某个表的所有触发器
alter table 表名 enable trigger all --禁用某个表的指定触发器
alter table 表名 disable trigger 触发器名
--启用某个表的指定触发器
alter table 表名 enable trigger 触发器名
分类: SqlServer 可惜不是mysqld
MYSQL如何禁用某个触发器
如题。
触发器存在,只是禁用。
类似alter table 表名 disable trigger 触发器名称
13.1.2. ALTER TABLE语法
ALTER [IGNORE] TABLE tbl_name
alter_specification [, alter_specification] ...
alter_specification:
ADD [COLUMN] column_definition [FIRST | AFTER col_name ]
| ADD [COLUMN] (column_definition,...)
| ADD INDEX [index_name] [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
PRIMARY KEY [index_type] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
UNIQUE [index_name] [index_type] (index_col_name,...)
| ADD [FULLTEXT|SPATIAL] [index_name] (index_col_name,...)
| ADD [CONSTRAINT [symbol]]
FOREIGN KEY [index_name] (index_col_name,...)
[reference_definition]
| ALTER [COLUMN] col_name {SET DEFAULT literal | DROP DEFAULT}
| CHANGE [COLUMN] old_col_name column_definition
[FIRST|AFTER col_name]
| MODIFY [COLUMN] column_definition [FIRST | AFTER col_name]
| DROP [COLUMN] col_name
| DROP PRIMARY KEY
| DROP INDEX index_name
| DROP FOREIGN KEY fk_symbol
| DISABLE KEYS
| ENABLE KEYS
| RENAME [TO] new_tbl_name
| ORDER BY col_name
| CONVERT TO CHARACTER SET charset_name [COLLATE collation_name]
| [DEFAULT] CHARACTER SET charset_name [COLLATE collation_name]
| DISCARD TABLESPACE
| IMPORT TABLESPACE
| table_options
| partition_options
| ADD PARTITION partition_definition
| DROP PARTITION partition_names
| COALESCE PARTITION number
| REORGANIZE PARTITION partition_names INTO (partition_definitions)
| ANALYZE PARTITION partition_names
| CHECK PARTITION partition_names
| OPTIMIZE PARTITION partition_names
| REBUILD PARTITION partition_names
| REPAIR PARTITION partition_names
MYSQL语法里没有。
或许有其他某些变通的方式可以实现。
感谢各位了。
TOP 回复 引用天天97
2楼 发表于 2010-09-07 18:13
SQL code
DROP TABLE IF EXISTS test ;
CREATE TABLE test (iss int);
delimiter $$
CREATE TRIGGER trIn
AFTER INSERT ON test
FOR each ROW
BEGIN
IF (select count(*) from information_schema.tables where table_name='xxx')=0
THEN
SET @n=1; ----你要做的操作
ELSE
SET @n=2; ----这里你可以随意写个东西 反正就是不触发本来的操作
END IF ;
END;
$$
delimiter ;
--test
mysql> set @N=0;
Query OK, 0 rows affected (0.00 sec)
mysql> create table xxx(a int);
Query OK, 0 rows affected (0.01 sec)
mysql> insert test select 1;
Query OK, 1 row affected (0.04 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select @n;
+------+
| @n |
+------+
| 2 |
+------+