转眼,从实习到毕业,来公司已经差不多有4个月了。在学校没学到什么东西,怪自己太懒,又没有钻研技术的那股精神。如今来公司做金蝶系列的插件开发,都显得很吃力。
之前在学校,数据库就学了一点毛皮,现在要学会写SQL存储过程,触发器,报表等高级SQL查询语句,下面给出自己学习写触发器的过程:
什么是触发器,从网上搜了一大堆资料后,总结如下:
触发器,顾名思义,通过触发来引发的一种执行语句。其实触发器也是一种特殊的存储过程,一般的存储过程是通过存程名直接调用,而触发器是通过事件进行触发而执行的。这是一个什么样的事件呢?主要分为增,删,改之类的执行事件 。当表中的数据发生变化时自动强制执行。常见的触发器有两种:
after(for) 表示执行代码后,执行触发器
instead of 表示执行代码前,用已经写好的触发器代替你的操作
触发器语法:
create trigger 触发器的名字 on 操作表
for|after instead of
update|insert|delete
as
SQL语句
触发器实现原理图
其中after触发器要求只有执行某一操作insert、update、delete之后触发器才被触发,且只能定义在表上。而instead of触发器表示并不执行其定义的操作(insert、update、delete)而仅是执行触发器本身。既可以在表上定义instead of触发器,也可以在视图上定义。
触发器有两个特殊的表:插入表(instered表)和删除表(deleted表)。这两张是逻辑表也是虚表。有系统在内存中创建者两张表,不会存储在数据库中。而且两张表的都是只读的,只能读取数据而不能修改数据。这两张表的结果总是与被改触发器应用的表的结构相同。当触发器完成工作后,这两张表就会被删除。Inserted表的数据是插入或是修改后的数据,而deleted表的数据是更新前的或是删除的数据。
对表的操作 |
Inserted逻辑表 |
Deleted逻辑表 |
增加记录(insert) |
存放增加的记录 |
无 |
删除记录(delete) |
无 |
存放被删除的记录 |
修改记录(update) |
存放更新后的记录 |
存放更新前的记录 |
Update数据的时候就是先删除表记录,然后增加一条记录。这样在inserted和deleted表就都有update后的数据记录了。注意的是:触发器本身就是一个事务,所以在触发器里面可以对修改数据进行一些特殊的检查。如果不满足可以利用事务回滚,撤销操作。
触发器示例
Example1
--禁止用户插入数据(实际上是先插入,然后立刻将其删除!)
create trigger tr_insert on bank
for --for表示执行之后的操作
insert --即先执行了插入操作,同时在临时表中保存了插入记录
as
--执行完插入之后,在新生成的表中将刚刚插入的那条记录删除,
--而此时得到的刚刚插入的记录的id是通过临时表 inserted得到的
delete * from bank where cid=(select cid from inserted)
生成上面的触发器后,当用户再输入insert语句后就见不到效果了!
如:insert into bank values('0004',10000),是插入不进数据库的。
Example2
--删除谁就让谁的账户加上10元
create trigger tr_dalete on bank
instead of
delete
as
update bank balance=balance+10 where cid=(select cid from deleted)
生成这个触发器之后,当用户输入delete语句后,对应的那个id不但没有被删除掉,而且他的账户增加了10元
如:delete from bank where cid='0002',执行完这句话后,编号为0002的账户会增加10元
经典案例eg:
触发器的简单实例
eg:禁止插入新的数据的触发器(先插入,再删除)
CREATE TRIGGER tr_insert ON scorerecord
FOR (after)
INSERT
AS
DELETE FROM scorerecord WHERE id =(SELECT id FROM inserted)
测试SQL语句 : INSERT INTO scorerecord VALUES('1018','1306','c#编程','98')
eg2:
CREATE TRIGGER tr_insert2 ON usertest
AFTER
INSERT
AS
DELETE FROM usertest WHERE Id_P =(SELECT Id_P FROM INSERTED)
测试SQL语句:INSERT INTO usertest(Id_P,Lastname,Firstname) VALUES('3','习大大','琢磨')
删除时候,自动加10分
eg1:
CREATE TRIGGER tr_delete ON scorerecord
INSTEAD OF
DELETE
as
UPDATE scorerecord SET score = score + 10 WHERE id = (SELECT id FROM deleted)
测试SQL语句:DELETE FROM dbo.scorerecord WHERE id = '26'
eg2:
CREATE TRIGGER tr_delete2 ON usertest
INSTEAD OF
DELETE
as
UPDATE usertest SET score = score+19 WHERE Id_P =(SELECT Id_P FROM deleted )
测试SQL语句:DELETE FROM usertest WHERE Id_P ='1'
另外测试:
--触发器的案例
--创建触发器
CREATE TRIGGER tr_mytrigger ON testdate2
AFTER UPDATE
as PRINT 'the table was update!'
--测试
UPDATE testdate2 SET Course = '历史' WHERE Course ='英语'
--修改触发器
ALTER TRIGGER tr_mytrigger ON testdate2
FOR UPDATE
as PRINT '一定要学会用触发器'
--测试
UPDATE testdate2 SET Score = Score + 60 WHERE username ='小明'
SELECT * FROM dbo.testdate2
--查看触发器内容
EXEC sp_helptext tr_mytrigger
--查询数据库中有多少触发器
SELECT * FROM sysobjects WHERE xtype ='tr'
select * from sys.triggers --查询数据库中触发器的名字 当不知道触发器名字的时候
select * from sysobjects where type='tr' and name='tr_mytrigger' --知道触发器的名字
--禁用触发器
DISABLE TRIGGER tr_mytrigger ON testdate2
--启用触发器
ENABLE TRIGGER tr_mytrigger ON testdate2
--触发器功能比较强大,但是一旦触发,恢复起来比较麻烦,那我们就需要对数据进行保护,这里需要用到rollback数据回滚
ALTER TRIGGER tr_mytrigger ON testdate2
AFTER UPDATE
as
IF EXISTS(SELECT * FROM testdate2 WHERE Score='156')
ROLLBACK
--测试
UPDATE testdate2 SET Score ='97' WHERE Score = '82' --有数据保护,触发器中止
- 还没有人评论,欢迎说说您的想法!