MySQL触发器如何正确使用,SQL中触发器的使用

创建触发器 是例外的存款和储蓄进程,自动实施,日常不要有再次来到值

MySQL触发器

触发器(trigger):监视某种情况,并触及某种操作。

触发器创立语法四要素:1.监视地方(table) 2.蹲点事件(insert/update/delete) 3.接触时间(after/before) 4.触发事件(insert/update/delete)

 

~~语法~~

CREATE T本田UR-VIGGE劲客 <触发器名称> --触发器必需盛名字,最多61个字符,或许前边会附有分隔符.它和MySQL中任何对象的命名方式基本相象.
{ BEFORE | AFTER } --触发器有施行的时间设置:能够安装为事件发生前或后。
{ INSERT | UPDATE | DELETE } --同样也能设定触发的风云:它们得以在实行insert、update或delete的历程中触发。
ON <表名称> --触发器是归于某八个表的:当在此个表上实行插入、 更新或删除操作的时候就诱致触发器的激活. 大家不能够给一样张表的同二个事变布署八个触发器。
FOR EACH ROW --触发器的实践间隔:FO揽胜极光 EACH ROW子句文告触发器 每间距风姿罗曼蒂克行实践叁回动作,并不是对整个表实践叁次。
<触发器SQL语句> --触发器包含所要触发的SQL语句:这里的说话能够是别的官方的讲话, 富含复合语句,但是此间的语句受的界定和函数的同风流罗曼蒂克。

 

create trigger triggerName

after/before insert/update/delete on 表名

for each row #那句话在mysql是原则性的

begin

sql语句;

end;

注:各自颜色对应上边的四要素。

第风流倜傥大家来创制两张表:

#商品表

create table g

(

  id int primary key auto_increment,

  name varchar(20),

  num int

);

#订单表

create table o

(

  oid int primary key auto_increment,

  gid int,

much int

);

insert into g(name,num) values('商品1',10),('商品2',10),('商品3',10);

 

假设大家在没利用触发器在此之前:假如我们今后卖了3个商品1,大家需求做两件事

1.往订单表插入一条记下

insert into o(gid,much) values(1,3);

2.创新商品表商品1的多余数量

update g set num=num-3 where id=1;

 

今天,大家来创设三个触发器:

亟待先进行该语句:delimiter $(意思是报告mysql语句的尾声换来以$停止)

create trigger tg1
after insert on o
for each row
begin
update g set num=num-3 where id=1;
end$

当时大家假设进行:

insert into o(gid,much) values(1,3)$

会发觉商品1的数目产生7了,说明在大家插入一条订单的时候,触发器自动帮大家做了更新操作。

 

但几天前会有贰个主题素材,因为大家触发器里面num和id都以写死的,所以无论我们买哪个商品,最后更新的都是商品1的多寡。举个例子:大家往订单表再插入一条记下:insert into o(gid,much) values(2,3),执行完后会发觉物品1的数目变4了,而商品2的数码没变,这样显著不是大家想要的结果。大家要求改善大家事先创设的触发器。

咱俩怎么在触发器援引行的值,约等于说大家要获得大家新插入的订单记录中的gid或much的值。

对于insert来说,新插入的行用new来代表,行中的每一列的值用new.列名来代表。

所以今后大家得以如此来改大家的触发器

create trigger tg2
after insert on o
for each row
begin
update g set num=num-new.much where id=new.gid;(注意此处和率先个触发器的分化)
end$

其次个触发器成立完成,大家先把第一个触发器删掉

drop trigger tg1$

再来测量检验一下,插入一条订单记录:insert into o(gid,much) values(2,3)$

试行完开掘商品2的多少形成7了,现在就对了。

 

这段时间还设有三种意况:

1.当客商撤废七个订单的时候,我们那边一直删除贰个订单,大家是或不是必要把相应的货品数量再加回去呢?

2.当顾客改良二个订单的数额时,大家触发器改良怎么写?

咱俩先解析一下先是种状态:

蹲点地点:o表

蹲点事件:delete

接触时间:after

接触事件:update

对于delete来讲:原来有风流倜傥行,后来被删去,想引用被删去的这后生可畏行,用old来表示,old.列名能够引用被剔除的行的值。

那我们的触发器就该这么写:

create trigger tg3

after delete on o

for each row

begin

update g set num = num old.much where id = old.gid;(注意那边的改变)

end$

创建达成。

再执行delete from o where oid = 2$

会意识商品2的数码又改为10了。

 

其次种状态:

蹲点地点:o表

蹲点事件:update

接触时间:after

接触事件:update

对此update来说:被改革的行,改过前的数目,用old来代表,old.列名援引被改变在此以前行中的值;

校勘的后的数据,用new来代表,new.列名援引被校勘现在行中的值。

那我们的触发器就该那样写:

create trigger tg4

after update on o

for each row

begin

update g set num = num old.much-new.much where id = old/new.gid;

end$

先把旧的数据苏醒再减去新的数据便是改良后的数量了。

我们来测量检验下:先把商品表和订单表的多少都清掉,易于测验。

尽管大家往商品表插入四个商品,数量都以10,

买3个商品1:insert into o(gid,much) values(1,3)$

那会儿商品1的数额改为7;

大家再改革插入的订单记录: update o set much = 5 where oid = 1$

我们改为买5个商品1,那个时候再查询商品表就能够开采商品1的多少只剩5了,表明大家的触发器发挥功能了。

 

要是:假使商品表有商品1,数量是10;

我们往订单表插入一条记下:

insert into o(gid,much) values(1,20);

会发觉货色1的数目改为-10了。这正是问题的处处,因为大家事先成立的触发器是after,也便是说触发的话语是在插入订单记录之后才实施的,那样我们就不可能判别新插入订单的进货数码。

 

先讲一下after和before的分别:

after是先完毕多少的增加和删除改,再触及,触发的言语晚于监视的增加和删除改操作,不只怕影响前边的增加和删除退换作;也正是说先插入订单记录,再改善商品的数码;

before是先成功触发,再增加和删除改,触发的言语先于监视的增加和删除改,我们就有空子判别,矫正就要产生的操作;

 

作者们用三个非凡案例来差别它们的界别,新建五个触发器:

#监视地点: 商品表o

#监视事件:insert

#接触时间:before

#接触事件:update

案例:当新扩充一条订单记录时,推断订单的货物数量,如若数据超过10,就私下认可改为10

create trigger tg6

before insert on o

for each row

begin

  if new.much > 10 then

    set new.much = 10;

  end if;

  update g set num = num - new.much where id = new.gid;

end$

实行完,把早前创建的after触发器删掉,再来插入一条订单记录:

insert into o(gid,much) valus(1,20)$

试行完会开采订单记录的多寡变成10,商品1的多寡变为0了,就不会忍俊不禁负数了。

 

触发器(trigger):监视某种景况,并触及某种操作。 触发器创建语法四要素:1.蹲点地点(table) 2.蹲点事件(insert/update/delete) 3.触发...

认知触发器:

以下的小说首要叙述的是怎么对MySQL触发器进行科学生运动用, MySQL数据库是在5.0 今后的连带版本中对MySQL触发器进行征引,一时也能够用相关的触发器对数码的完整性实行维护。如作者有二个表ge_element。

 

       触发器是风流倜傥种特有的仓库储存进程,它不可能被出示的调用,而是在往表中插入记录,改正记录可能去除记录时,被电动激活。在触发器中能够查询别的表,也得以实行更头昏眼花的T-SQL语句。假设实行的T-SQL语句推行了三个违规操作,则能够经过回滚事务使语句不能够推行,并赶回到事情奉行前的事态,Microsoft SQL Server 允许为别的给定的 INSERT、UPDATEDELETE 语句创设多少个触发器。

该表中有三个region_id,对应到ge_region表中的id,但是,region_id是足感觉空的,所以不应该设置外键约束,而作者在剔除ge_region表中的记录时,希望把在ge_element表中被引述到的笔录的region_id设为0,因为从没数据库的外键约束,笔者必须要在前后相继中操作,但自己又不想通进程序来操作,因为援用region_id的表可能不只ge_element一个,此时,就或然用到MySQL触发器,在剔除ge_region表中的记录时,把被引述的表中的region_id设为0。

类型:

触发器的作用:

1.创制触发器的语句:

  1.后触发器 (AFTE奇骏,FO奇骏卡塔 尔(阿拉伯语:قطر‎先实行对应语句,后实践触发器中的语句

◎触发器能够对数据库实行级联修正

CREATE T凯雷德IGGEEnclave <触发器名称> <--

  2.前触发器  并从未真的的实施触发语句(insert,update,delete卡塔 尔(阿拉伯语:قطر‎,而是进行触发后的语句

◎触发器能够变成比CKECK限制更眼花缭乱的界定

{ BEFORE | AFTER }

  3.行级触发器 (FOOdyssey EACH ROW卡塔尔 在SQL server 中空中楼阁

◎触发器能够窥见改换前后表中数据的两样,并基于那么些不一致来开展对应的操作。

{ INSERT | UPDATE | DELETE }

 

◎对于三个表上的两样操作(INSERT,UPDATE大概DELETE卡塔 尔(英语:State of Qatar)能够使用分歧的触发器,尽管是对相像的言辞也得以调用不一致的触发器完毕不一样的操作。

ON <表名称>

商品号为1的仓库储存量:

◎达成一定的工作法则。

FOR EACH ROW

图片 1

 

<触发器SQL语句>

 

 

触发器必须盛名字,最多柒十个字符,或者后边会附有分隔符.它和MySQL中任何对象的命名方式为主相象.

1.后触发器(达成不相同表之间的限定卡塔尔国


这里自个儿有个习于旧贯:正是用表的名字+'_'+触发器类型的缩写.因而后生可畏旦是表t26,触发器是在事件UPDATE参谋上边包车型大巴点2卡塔 尔(阿拉伯语:قطر‎和3卡塔 尔(阿拉伯语:قطر‎卡塔 尔(英语:State of Qatar)以前BEFORE卡塔尔的,那么它的名字正是t26_bu。

 

语法:   CREATE TRIGGER trigger_name
ON { table | view }
[ WITH ENCRYPTION ]
{
    { { FOR | AFTER | INSTEAD OF } { [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NOT FOR REPLICATION ]
        AS
        [ { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ]
        sql_statement [ ...n ]
    }
}

能够直接在EMS中创立触发器。

--实现在销售量不大于库存量时,每卖出n件商品,对应商品的库存要减n,若销售量大于库存量,则回滚此次操作
IF EXISTS (SELECT *FROM sysobjects WHERE name='tr_SaleCommodity')
    DROP TRIGGER tr_SaleCommodity
GO
CREATE TRIGGER tr_SaleCommodity
ON OrderInfo FOR INSERT  --FOR/AFTER为后触发器
AS
    BEGIN
        IF EXISTS (
            SELECT  * FROM inserted I INNER JOIN CommodityInfo C ON I.CommodityId=C.CommodityId
            WHERE I.Amount>C.Amount
        )
            BEGIN
                ROLLBACK  --后触发器
                PRINT '商品的销售量大于商品的库存量'
            END    
        ELSE
            BEGIN
                UPDATE CommodityInfo
                SET Amount=Amount-(SELECT Amount FROM inserted)
                WHERE CommodityId IN
                (
                    SELECT CommodityId FROM inserted
                )
            END
    END
GO

2.触及时间:

执行:

AFTER
点名触发器独有在触发 SQL 语句中钦命的持有操作都已成功实行后才激起。全部的引用级联操作和束缚检查也亟须成功做到后,才具实施此触发器。
只要仅钦点 FO奇骏 关键字,则 AFTEEscort 是私下认可设置。
不能够在视图上定义 AFTE奥迪Q5 触发器。
INSTEAD OF
钦点实践触发器并非实行触发 SQL 语句,进而代替触发语句的操作。
在表或视图上,每一个 INSERT、UPDATE 或 DELETE 语句最多能够定义二个 INSTEAD OF 触发器。不过,能够在每一个具备 INSTEAD OF 触发器的视图上定义视图。
INSTEAD OF 触发器无法在 WITH CHECK OPTION 的可更新视图上定义。如若向钦点了 WITH CHECK OPTION 选项的可更新视图增多 INSTEAD OF 触发器,SQL Server 将发生八个八花九裂。客户必须用 ALTEEscort VIEW 删除该选项后手艺定义 INSTEAD OF 触发器。
{ [DELETE] [,] [INSERT] [,] [UPDATE] }
是钦点在表或视图上实践怎么样数据订正语句时将激活触发器的主要性字。必须最少钦赐一个筛选。在触发器定义中允许使用以随机顺序组合的那个根本字。即使内定的选项多于叁个,需用逗号分隔这几个选拔。
对此 INSTEAD OF 触发器,不容许在富有 ON DELETE 级联操作援用关系的表上使用 DELETE 选项。同样,也差别意在有着 ON UPDATE 级联操作援引关系的表上使用 UPDATE 选项。

Before表示在事件时有发生早先推行MySQL触发器,After代表在事件爆发以往推行触发器;

INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayMoney,PayWay,OrderTime,Confirm,SendGoods)
VALUES('YOUYOU',1,10,600,'网上银行','2014-11-11 00:00:00.000',1,1)

3.触发事件:

结果:

例1:

八个事件:insert, update, delete

图片 2

在Goods表中国建工业总会公司立删除触发器,完毕Goods表和Orders表中的级联删除。(注:那么些意义在SQLSEVE瑞虎二〇〇三以前只好用触发器达成)

4.触发器与表的关系:

  注意:1.上大器晚成行为发售记录,下生龙活虎行为货色1的信息

 

触发器是归于三个表的,当在此个表上试行insert, update, delete操作时,就能够造成相应的触发器被激活;

     2.卖出十二个,仓库储存量由48化为38 

CREATE TRIGGER GoodsDelete
ON Goods    --在其上实施触发器

无法给同三个表的同三个操作创立七个例外的触发器。

       3.能够看看以上的发售记录中的Paymoney是不得法的,它的值应该是Amount*OutPrice=10*300,所以必要前触发器来约束

AFTER DELETE
AS
DELETE FROM Orders
WHERE GoodsName IN
(SELECT Name FORM deleted)

5.触发间距:

 


FO奥德赛 EACH ROW 子句公告MySQL触发器每间隔生机勃勃行实施三回动作,实际不是对整下表施行贰回。

2.前触发器(能够兑现行级触发器功能卡塔 尔(英语:State of Qatar)

例2:在Orders表上树立叁个AFTEEvoque触发器,当向Orders表中插入风度翩翩行(参加一条订单记录卡塔尔时,检查订单中的物品是还是不是在整合治理中(查六柱预测应物品在Goods表中的状态是否为1卡塔 尔(阿拉伯语:قطر‎,借使在照管中,则不能够下订单。

6.触发的SQL语句:

 

CREATE TRIGGER OrderInsert
ON Orders
AFTER INSERT
AS
IF(SELECT Status FROM Goods,Insertd WHERE Goods.Name=inserted.Name)=1
BEGIN
   P瑞虎INT '物品正在收拾中,无法在Orders表中增添该商品记录。'
   ROLLBACK TRANSACTION
END

触发器包蕴所要触发的SQL语句:这里的说话能够是其它合法的说话,包蕴复合语句,然则这里的语句受的界定和函数的如出生龙活虎辙。

--实现了日期校验和支付金额的计算
IF EXISTS(SELECT* FROM sysobjects WHERE name='tr_DateConfim')
    DROP TRIGGER tr_DateConfim
GO
CREATE TRIGGER tr_DateConfim
ON OrderInfo INSTEAD OF INSERT ,UPDATE
AS
    BEGIN
        DECLARE @date datetime
        SELECT @date=OrderTime FROM inserted
        IF @date BETWEEN '2012-1-1' AND '2015-1-1'
            BEGIN
                DECLARE @UserId varchar(20) ,@CommodityId int,@Amount int,@PayMoney money,@PayWay varchar(20),@OrderTime datetime,@Confirm int,@SendGoods int
                SELECT @UserId=UserId,@CommodityId=CommodityId,@Amount=Amount,@PayWay=PayWay,@OrderTime=OrderTime,@Confirm=Confirm,@SendGoods=SendGoods FROM inserted
                DECLARE @outPrice money
                SELECT @outPrice=OutPrice FROM CommodityInfo WHERE CommodityId=@CommodityId
                SET @PayMoney=@outPrice*@Amount
                PRINT 'inserted 中的数据:' CONVERT(varchar(20),@UserId) ' ' CONVERT(varchar(20),@CommodityId) ' ' CONVERT(varchar(20),@Amount) ' ' CONVERT(varchar(20),@PayMoney) ' ' CONVERT(varchar(20),@PayWay) ' ' CONVERT(varchar(20),@OrderTime) ' ' CONVERT(varchar(20),@Confirm) ' ' CONVERT(varchar(20),@SendGoods) ' ' CONVERT(varchar(20),@outPrice)
                INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayMoney,PayWay,OrderTime,Confirm,SendGoods)
                SELECT UserId,CommodityId,Amount,@PayMoney,PayWay,OrderTime,Confirm,SendGoods FROM inserted
            END
        ELSE 
            PRINT '你插入的数据中的时间只能在 2012-1-1 到 2015-1-1 中间'
    END
GO

复合语句(BEGIN / END)是法定的.

执行:

例3:在Orders表上建构一个插入触发器,在抬高四个订单时,收缩Goods表相应货色记录的仓库储存量

流动调查整Flow-of-control卡塔 尔(阿拉伯语:قطر‎语句(IF, CASE, WHILE, LOOP, WHILE, REPEAT, LEAVE,ITERATE)也是法定的.

INSERT INTO OrderInfo(UserId,CommodityId,Amount,PayWay,OrderTime,Confirm,SendGoods)
VALUES('YOUYOU',1,5,'网上银行','2013-1-11',1,1)

CREATE TRIGGER OrdersInsert_1
ON Orders
AFTER INSERT
AS
UPDATE Goods SET Storage=Storage-inserted.Quantity
FROM Goods,inserted
WHERE Goods.Name=inserted.GoodsName

变量注明(DECLARE)以致派出(SET)是法定的.

   注意:这里插入时作者并从未概念PayMoney,PayMoney是经过触发器来机关总结的

 

允许规范表明.

结果:


非常管理评释也是同意的.

日期不得法:

例4 限制Orders表的订单日期(OrderDate)不可能手工业改善。

只是在这里间要铭记函数有受限条件:不能够在函数中做客表.由此在函数中使用以下语句是不法的。

图片 3

CREATE TRIGGER OrderDataUpDat(e
ON Orders
AFTER UPDATE
IF UPDATE(OrderDate)
BEGIN
RAISERRO大切诺基('不能够手动改良',10,1)
ROLLBACK TRANSACTION
END

7.成立触发器的权位:

 

 

你应当要有比较大的权力工夫够创制MySQL触发器;作者在创建触发器的时候唤醒要有super privilege能力够创设;

日子正确:


MySQL数据库是在5.0 现在的有关版本中对MySQL触发器实行援用,一时也能够用相关...

打字与印刷音信对应:@UserId ' ' @CommodityId ' ' @Amount ' ' @PayMoney ' ' @PayWay ' '@Order提姆e ' '@Confirm ' ' @SendGoods ' '@outPrice

例5:推断要插入的订单中的货色名称在Goods表中是不是留存,借使子虚乌有则回滚事务.

图片 4

 

图片 5

CREATE TRIGGER OrderInsert_2
ON Orders
AFTER INSERT
AS
IF (SELECT COUNT(*) FORM Goods,inserted
WHERE Goods.Name=inserted.GoodName)=0
BEGIN
P奥迪Q7INT('没有这种物品')
ROLLBACK TRAINSACTION
END

 


3.行级触发器(错误卡塔尔

例6:INSTEAD OF触发器用于代替引起触发器实施的T-SQL语句。每当向SimpleCustomers视图施行INSERT语句时,Insertcustmer触发器被触发,这个时候Inserted表中风流倜傥度有了要插入的数目,在Insertcustmer触发器的代码中,解析插入顾客姓名的字符串,将其分拆为姓和名四个字符串,并插入Customers表,而原先的INSERT语句无法执行。

  图片 6

创建SimpleCustomers视图

实践结果:

经过SimpleCustomers视图向Customers表中增添记录

图片 7

GO
CREATE VIEW SimpleCustomers(CustomerName,city,Tel)
AS
SELECT FistName ',' LastName,City,Tel
FROM Customers
GO
CREATE TIRGGER Insertcustmer
ON SimpleCustomers
INSTEAD OF INSERT
AS
DECLARE @Name varchar(40)
DECLARE @FistName varchar(20)
DECLARE @LastName varchar(20)
DECLARE @City varchar(20)
DECLARE @Tel varchar(20)
DECLARE @idx int
BEGIN
SELECT @Name=CustomerName,@City=City,@Tel=Tel
FROM inserted
SET @idx=CHARINDEX(',',@Name)
SET @FistName=LEFT(@Name,@idx-1)
SET @LastNme=RIGHT(@Name,@LEN(@Name)-@idx)
INSERT INTO Customers
(FistName,LastName,City,Tel)
VALUES(@FistName,@LastName,@City,@Tel)
END

能够看看在SQL server中并不协理行级触发器


本文由星彩网app下载发布于星彩彩票app下载,转载请注明出处:MySQL触发器如何正确使用,SQL中触发器的使用

TAG标签:
Ctrl+D 将本页面保存为书签,全面了解最新资讯,方便快捷。