作相应的处理,触发器可以从 DBA

作者: www.9159.com  发布:2019-11-20

之前有个同学问我,本地数据库插入新数据时怎么同步到服务器上,当时我先想到是程序逻辑控制,作相应的处理。

触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程www.9159.com,,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。SQL3的触发器是一个能由系统自动执行对数据库修改的语句。

存储过程之六—触发器,存储过程之六

一、触发器

  触发器是与表有关的命名数据库对象,当表上出现特定事件时,将调用该对象。它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,比如当对一个表进行操作( insert,delete, update)时就会激活它执行。

  触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。
触发器有一个非常好的特性就是:触发器可以禁止或回滚违反引用完整性的更改,从而取消所尝试的数据修改。

  1、创建

    语法:

    CREATE TRIGGER trigger_name trigger_time trigger_event ON tbl_name FOR EACH ROW trigger_stmt

    trigger_time:是触发器的动作时间。它可以是BEFORE或AFTER,以指明触发器是在激活它的语句之前或之后触发。

    trigger_event: 指明了激活触发器的语句的类型。trigger_event可以是如下之中的一个:

  • INSERT:将新行插入表时激活触发器,例如,通过INSERT、LOAD DATA和REPLACE语句。
  • UPDATE:更改某一行时激活触发器,例如,通过UPDATE语句。
  • DELETE:从表中删除某一行时激活触发器,例如,通过DELETE和REPLACE语句。

  对于具有相同触发器动作时间和事件的给定表,不能有两个触发器。如,对于某一表,不能有两个BEFORE UPDATE触发器。但可以有1个BEFORE UPDATE触发器和1个BEFORE INSERT触发器,或1个BEFORE UPDATE触发器和1个AFTER UPDATE触发器。

    trigger_stmt:是当触发器激活时执行的语句。如果你打算执行多个语句,可使用BEGIN ... END复合语句结构。这样,能够定义执行多条语句的触发器。

触发器不能调用将数据返回客户端的存储程序,也不能使用采用CALL语句的动态SQL(允许存储程序通过参数将数据返回触发器)。触发器不能使用以显式或隐式方式开始或结束事务的语句,如START TRANSACTION、COMMIT或ROLLBACK。

  2、查看

SHOW TRIGGERS; -- 查看所有触发器 
SHOW CREATE TRIGGER ins_sum;-- 查看具体触发器

  3、删除 

DROP TRIGGER trigger_name; -- 删除具体触发器

  注释:从MySQL 5.0.10之前的MySQL版本升级到5.0.10或更高版本时(包括所有的MySQL 5.1版本),必须在升级之前舍弃所有的触发器,并在随后重新创建它们,否则,在升级之后DROP TRIGGER不工作。

  示例:

DROP TABLE IF EXISTS `account`;
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;-- 将插入amount列的值加起来。

SET @sum = 0;
INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
SELECT @sum AS 'Total amount inserted';

执行了INSERT语句后,@sum的值是14.98 + 1937.50 – 100,或1852.48。
执行结果如下:

二、old与NEW

  关键字new和OLD的区别:

INSERT:只有 NEW
UPDATE: BEFORE OLD 、 AFTER NEW
DELETE: 只有OLD

  用OLD命名的列是只读的。你可以引用它,但不能更改它。对于用NEW命名的列,如果具有SELECT权限,可引用它。 可使用“SET NEW.col_name = value”更改它的值。这意味着,你可以使用触发器来更改将要插入到新行中的值,或用于更新行的值。OLD和NEW是对触发器的MySQL扩展。

三、实例

  表结构如下: 

DROP TABLE IF EXISTS `person`;
CREATE TABLE `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `password` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;

   1、insert 

DROP TRIGGER IF EXISTS trigger_insert;
-- before
CREATE TRIGGER trigger_insert BEFORE INSERT ON person
FOR EACH ROW  
SET @info = new.username, new.age = new.age + 2; 

INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');
SELECT @info;

   插入之前被调用,age被加1,也记录了加入记录的username。insert 只有before才能修改new.age的值,after中,无法修改。

  2、update 

 1 DROP TRIGGER IF EXISTS trigger_update_before;
 2 CREATE TRIGGER trigger_update_before BEFORE UPDATE ON person
 3 FOR EACH ROW 
 4 BEGIN
 5 SET @infoname1 = new.username; -- 记录新名字
 6 SET new.age = new.age + 3; -- 修改年龄
 7 END;
 8 
 9 DROP TRIGGER IF EXISTS trigger_update_after;
10 CREATE TRIGGER trigger_update_after AFTER UPDATE ON person
11 FOR EACH ROW  
12 SET @infoname2 =  old.username;  -- 记录修改前的名字
13 
14 SELECT * FROM person WHERE id = 1;
15 UPDATE person SET username='lisi', age = 0 WHERE id = 1;
16 SELECT * FROM person WHERE id = 1;
17 SELECT @infoname1 AS newname, @infoname2 AS oldname;

  执行完14行结果如下:

www.9159.com 1

   执行完15、16行结果如下:

www.9159.com 2

  年龄已被触发器更改

  执行完17行后结果如下:

www.9159.com 3

  触发器已获取更改前后的用户名称。

  3、delete

1 DROP TRIGGER IF EXISTS trigger_delete_after;
2 CREATE TRIGGER trigger_delete_after AFTER DELETE ON person
3 FOR EACH ROW  
4 SET @infoname3 =  old.username;  -- 获取被删除的用户名称
5  
6 DELETE FROM person WHERE id = 1;
7 SELECT * FROM person WHERE id = 1;
8 SELECT @infoname3 AS oldname;

  执行完第7行结果如下:

www.9159.com 4

  记录已经被删除

  执行完第8行结果如下:

www.9159.com 5

  显示已被删除的记录的用户名称。

  4、触发器调用存储过程

  在触发器中通过使用BEGIN ... END结构,能够定义执行多条语句的触发器。在BEGIN块中,还能使用其他语法,如条件和循环等。我们可以将这些语句封装到存储过程里面,供触发器调用。

  实例如下:

 1 DROP TABLE IF EXISTS `person`;
 2 CREATE TABLE `person` (
 3   `id` int(11) NOT NULL AUTO_INCREMENT,
 4   `username` varchar(255) DEFAULT NULL,
 5   `age` int(11) DEFAULT NULL,
 6   `password` varchar(255) DEFAULT NULL,
 7   PRIMARY KEY (`id`)
 8 ) ENGINE=InnoDB AUTO_INCREMENT=18 DEFAULT CHARSET=utf8;
 9 INSERT INTO person (username, age, password) VALUES ( 'zhangsan', '21', '123456');
10 
11 DROP PROCEDURE IF EXISTS pro_person_update;
12 CREATE PROCEDURE pro_person_update(
13     INOUT age INT(11)
14 )
15 BEGIN
16     IF age < 0 THEN -- 年龄小于0
17         SET age = 0;
18     ELSEIF age > 100 THEN -- 年龄大于100
19         SET age= 100;
20     END IF;
21 END;
22 
23 DROP TRIGGER IF EXISTS trgger_proceduce_person_update;
24 CREATE TRIGGER trgger_proceduce_person_update BEFORE UPDATE ON person
25 FOR EACH ROW 
26     CALL pro_person_update(new.age);
27 
28 SELECT * FROM person WHERE id = 1;
29 UPDATE person SET age= -3 WHERE id=1;
30 SELECT * FROM person WHERE id = 1;

  由于上面的三个例子生成的触发器会对本例有影响,所以重新创建表结构。

  执行完第28行结果如下:

www.9159.com 6

  执行完第29,30行后,结果如下:

www.9159.com 7

  age为0,存储过程已经被update before触发器调用。

  在触发器的执行过程中,MySQL处理错误的方式如下:

  • 如果BEFORE触发器执行失败,sql语句也会执行失败。
  • 仅当BEFORE触发器(如果有的话)和sql语句执行成功,才执行AFTER触发器。
  • 如果在BEFORE或AFTER触发器的执行过程中出现错误,将导致调用触发器的整个语句的失败。
  • 对于事务性表(在 innodb上所建立的表是事务性表,是事务安全的。),如果触发器失败(以及由此导致的整个语句的失败),该语句所执行的所有更改将回滚。对于非事务性表,不能执行这类回滚,因而,即使语句失败,失败之前所作的任何更改依然有效。

但有时候我们程序不太好处理,那能不能从数据库入手呢,数据库不是有触发器(Trigger)吗,应该是可以的,这里就用这个来做吧。一些东西也是不太懂,网上找的资料,感谢那些人默默奉献的好人。如有误解,欢迎各位大神指正!

触发器可以查询其他表,而且可以包含复杂的SQL语句。它们主要用于强制服从复杂的业务规则或要求。例如:您可以根据客户当前的帐户状态,控制是否允许插入新订单。

存储过程与触发器到底是个什玩意?

存储过程相当于打包好的sql语法,可以包含复杂的sql操作,在程序调用时只要执行该存储过程,一句话就可以完成复杂的数据库操作.
触发器是也是打包好的sql语法,只不过执行它的条件是当被设定改触发器的表有变化的时候.比如我可以写一个触发器,设定它在插入一笔数据到这个表里的时候,去同步另一个表.那么在程序里我只要做到往数据库里插一笔数据,那么另一个表就可以同时被更新.
用存储过程和数据库的好处是,可以充分利用数据库资源,减少程序代码,程序员的工作将更简便,写出来的代码也更简洁明了.当然要真正明白程序在做什么,还是要到存储过程和触发器里面看明白.
呵呵,这些都是本人自身体会,你可以有选择的看看.  

1、首先,了解什么是触发器

触发器也可用于强制引用完整性,以便在多个表中添加、更新或删除行时,保留在这些表之间所定义的关系。然而,强制引用完整性的最好方法是在相关表中定义主键和外键约束。如果使用数据库关系图,则可以在表之间创建关系以自动创建外键约束。

怎在存储过程中建立触发器

(1).存储过程:
create procedure for_select(你想用的触发器名字)
delare @name char(8) output(用于输出的),@age int output(用于输出),@phone_num char(11) (输入)
as
select @name=name(列名),@age=age(列名) from table_name(表名)
where phone_num(列名)[email protected]_num

(2)触发器
create tigger for_update
on table_name
after update
as
if(update(phone_num))
update table_name2
set phone_num=i.phone_num
from deleted d,inserted i
where table_name.phone_num=d.phone_num
end
其中:deleted与inserted是sql中默认的临时表。用来储存原来的值和插入的值。本触发器的作用是 当更新table_name 中的phone_num时table_name1中的phone_num也一起更新!注意触发器只能用于update.insert.delete.select中不能用于创建表............  

一、触发器 触发器是与表有关的命名数据库对象,当表上出现特定事件时,将调用该对象。它是与...

触发器(trigger)是SQL server 提供给程序员和数据分析员来保证数据完整性的一种方法,它是与表事件相关的特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由事件来触发,当对一个表进行操作( insert,delete, update)时就会激活它执行。触发器经常用于加强数据的完整性约束和业务规则等。 触发器可以从 DBA_TRIGGERS ,USER_TRIGGERS 数据字典中查到。

触发器与存储过程的唯一区别是触发器不能执行EXECUTE语句调用,而是在用户执行Transact-SQL语句时自动触发执行。

简单讲下创建trigger的语法:

CREATE TRIGGER trigger_name
 ON table_name
 [WITH ENCRYPTION]
  FOR | AFTER | INSTEAD OF [DELETE, INSERT, UPDATE]
 AS 
  T-SQL语句
GO

 2、怎么连接到服务器的数据库

select * from sys.servers --查看当前的服务
EXEC sp_addlinkedserver
@server='black',--被访问的服务器别名(习惯上直接使用目标服务器IP,或取个别名如:black)
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.1.36', --要访问的服务器
@catalog='anmax' --数据库名称
exec sp_addlinkedsrvlogin 'black','false',null,'sa','sa123456' --(balck->前面取的别名;sa->数据库用户名;sa123456->密码)
select top 10 * from [black].[anmax].dbo.ad --测试是否成功
exec sp_droplinkedsrvlogin 'black',null --移除登陆用户
exec sp_dropserver 'black' --移除服务

 3、同步插入的数据

create trigger trgtest
on product
after insert
as 
begin
--select *  from sys.servers --查看当前的服务
EXEC sp_addlinkedserver
@server='black',--被访问的服务器别名(习惯上直接使用目标服务器IP,或取个别名如:JOY)
@srvproduct='',
@provider='SQLOLEDB',
@datasrc='192.168.1.36', --要访问的服务器
@catalog='anmax' --数据库名称
exec sp_addlinkedsrvlogin 'black','false',null,'sa','sa123456'
--执行同步代码
declare @proId int;
set @proId=@@identity;--插入的数据ID
select * into [你服务器表] from product where productid=@Proid

exec sp_droplinkedsrvlogin 'black',null
exec sp_dropserver 'black'

end

期间遇到些问题:开启TCP/IP协议

 www.9159.com 8

 

本文由9159.com发布于www.9159.com,转载请注明出处:作相应的处理,触发器可以从 DBA

关键词: