触发器.docx
- 文档编号:25077603
- 上传时间:2023-06-04
- 格式:DOCX
- 页数:25
- 大小:21.98KB
触发器.docx
《触发器.docx》由会员分享,可在线阅读,更多相关《触发器.docx(25页珍藏版)》请在冰豆网上搜索。
触发器
第12章触发器
例12-1发送通知邮件
CREATETRIGGERreminder
ONbooksFORINSERT,UPDATE,DELETE
AS
EXECmaster..xp_sendmail'kelvin',
'表books被更改.'
例12-2INSTEADOF触发器
createtriggerf
ontbl
insteadofdelete
as
insertintoLogs...
例12-3触发器中IFUPDATE应用
createtriggerf
on tbl
for update
as
ifupdate(t1) orupdate(t2)
sql_statement--更新了表tb1中t1或t2列时要执行的语句
inserted、deleted
例12-4虚表inserted和deleted
createtriggertbl_delete
ontbl
fordelete
as
declare@titlevarchar(200)
select@title=titlefromdeleted
insertintoLogs(logContent)values('删除了title为:
'+title+'的记录')
创建t1表
CREATETABLE[dbo].[t1]
(
[id][int]NOTNULL,
[price][float]NOTNULL
)
创建t2表
CREATETABLE[dbo].[t2]
(
[id][int]NOTNULL,
[qt][float]NOTNULL,
[sum][float]NULL
)
例12-5创建触发器
USE[TestDB1]
GO
/******Object:
Trigger[dbo].[tg_sum]ScriptDate:
10/18/200718:
13:
43******/
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
ALTERTrigger[dbo].[tg_sum]
On[dbo].[t2]
ForInsert,Update
As
Updatet2Setpsum=qt*priceFromt2InnerJoint1Ont2.id=t1.id
例12-6更新t2表中的数据
useTestDB1
GO
Updatet2setqt=6whereid=1
例12-7查询某个数据库中的所有触发器
useTestDB1
GO
select*fromsysobjectswherextype='TR'
例12-8使用SQL语句查看触发器tg_sum的内容
useTestDB1
GO
execsp_helptext'tg_sum'
例12-9修改触发器的内容
ALTERTrigger[dbo].[tg_sum]
On[dbo].[t2]
ForUpdate
As
Updatet2Setpsum=qt*priceFromt2InnerJoint1Ont2.id=t1.id
例12-11禁用触发器tg_sum
useTestDB1
GO
altertablet2Disabletriggertg_sum
例12-12删除触发器
useTestDB1
GO
droptriggertg_sum
例12-13创建操作记录表
USETestDB1
go
createtable操作记录表
(
编号[int]IDENTITY(1,1)NOTNULL,
操作表名[varchar](20)NOTNULL,
操作语句[varchar](200)NOTNULL,
操作内容[varchar](200)NOTNULL,
操作时间[datetime]NOTNULL
CONSTRAINTDF_操作记录表_操作时间DEFAULT(getdate()),
CONSTRAINTPK_操作记录表PRIMARYKEYCLUSTERED(
编号ASC
)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]
)ON[PRIMARY]
GO
例12-14在数据表上创建触发器
USETestDB1
GO
CREATETRIGGER操作记录表_Insert
ON操作记录表
AFTERINSERT
AS
BEGIN
PRINT'数据库中插入了记录'
END
GO
例12-15创建类别表
USE[TestDB1]
GO
SETANSI_NULLSON
GO
SETQUOTED_IDENTIFIERON
GO
CREATETABLE[dbo].[类别](
[类别名称][nvarchar](50)NOTNULL,
[说明][nvarchar](50)NULL
)ON[PRIMARY]
例12-16在类别表上创建触发器
USETestDB1
GO
CREATETRIGGER类别_Insert
ON类别
AFTERINSERT
AS
BEGIN
DECLARE@类别名称nvarchar(20),
@说明nvarchar(max)
set@类别名称=(select类别名称frominserted)
set@说明=(select说明frominserted)
INSERTINTO操作记录表(操作表名,操作语句,操作内容)
values('类别表','插入记录','类别名称:
'+@类别名称+',说明:
'+@说明)
END
GO
例12-17检测表t的第二和第三列是否修改过
CREATETRIGGERtr_tON[t]AFTERUPDATE
AS
IF(COLUMNS_UPDATED()=6)
BEGIN
RAISERROR(‘Cannotchangebotht2andt3atthesametime’,16,1)
ROLLBACKTRAN
END
GO
例12-18触发器回滚
use数据库名
go
createtriggertr
on表名
forupdate
as
ifupdate(userName)
rollbacktran
例12-19
INSERTemployeeVALUES('XYZ12345M','New','M','Employee',1,1,'9952','6/1/95')
//CausestriggertofireandROLLBACKTRANSACTION.
DELETEemployeeWHEREemp_id='PMA42628M'
GO
例12-20
/*StartofTransaction*/
BEGINTRANSACTION
UPDATEemployeeSEThire_date='7/1/94'WHEREemp_id='VPA30890F'
INSERTemployeeVALUES('XYZ12345M','New','M','Employee',1,1,'9952','6/1/95')
//CausestriggertofireandROLLBACKTRANSACTION
例12-21有条件的INSERT触发器
CREATE TRIGGER employee_Trigger
ON dbo.employee
FOR INSERT/* INSERT, UPDATE, DELETE */
AS
BEGIN
declare @bh int
SELECT @bh=bh FROM INSERTED
INSERT INTO t_edu_lesson (bh, in_service ) valueS (@bh , ''1'')
END
例12-22
USETestDB1
GO
CREATETRIGGERproduct_Insert
On产品
AFTERINSERT
AS
BEGIN
PRINT'addoneproductsuccessfully'
END
GO
例12-23
USETestDB1
GO
CREATETRIGGERproduct_Insert1
On产品
AFTERINSERT
AS
BEGIN
PRINT'tellyouonceagain,addoneproductsuccessfully'
END
GO
例12-24改变触发器顺序
EXECSP_SETTRIGGERORDER
'product_Insert1','First','Insert'
go
EXECSP_SETTRIGGERORDER
'product_Insert','Last','Insert'
go
例12-25使用INSTEADOF触发器
REATETRIGGER订单_Insert
ON 订单
InsteadOfINSERT
AS
BEGIN
SETNOCOUNTON;
declare
@订单IDint,
@产品IDint,
@单价money,
@数量smallint,
@折扣real
set@订单ID=(select订单IDfrominserted)
set@产品ID=(select产品IDfrominserted)
set@单价=(select单价frominserted)
set@数量=(select数量frominserted)
set@折扣=(select折扣frominserted)
if(@折扣)>0.6
print'折扣不能大于.6'
else
INSERTINTO订单(订单ID,产品ID,单价,数量,折扣)
VALUES(@订单ID,@产品ID,@单价,@数量,@折扣)
END
GO
例12-26包含错误处理函数的存储过程
USETestDB1
GO
--Verifythatthestoredproceduredoesnotalreadyexist.
IFOBJECT_ID('usp_GetErrorInfo','P')ISNOTNULL
DROPPROCEDUREusp_GetErrorInfo;
GO
--Createproceduretoretrieveerrorinformation.
CREATEPROCEDUREusp_GetErrorInfo
AS
SELECT
ERROR_NUMBER()ASErrorNumber,
ERROR_SEVERITY()ASErrorSeverity,
ERROR_STATE()ASErrorState,
ERROR_PROCEDURE()ASErrorProcedure,
ERROR_LINE()ASErrorLine,
ERROR_MESSAGE()ASErrorMessage;
GO
BEGINTRY
--Generatedivide-by-zeroerror.
SELECT1/0;
ENDTRY
BEGINCATCH
--Executeerrorretrievalroutine.
EXECUTEusp_GetErrorInfo;
ENDCATCH;
例12-27
USEAdventureWorks;
GO
BEGINTRY
--Tabledoesnotexist;objectnameresolution
--errornotcaught.
SELECT*FROMNonexistentTable;
ENDTRY
BEGINCATCH
SELECT
ERROR_NUMBER()asErrorNumber,
ERROR_MESSAGE()asErrorMessage;
ENDCATCH
例12-28
--Verifythatthestoredproceduredoesnotexist.
IFOBJECT_ID(N'usp_ExampleProc',N'P')ISNOTNULL
DROPPROCEDUREusp_ExampleProc;
GO
--Createastoredprocedurethatwillcausean
--objectresolutionerror.
CREATEPROCEDUREusp_ExampleProc
AS
SELECT*FROMNonexistentTable;
GO
BEGINTRY
EXECUTEusp_ExampleProc
ENDTRY
BEGINCATCH
SELECT
ERROR_NUMBER()asErrorNumber,
ERROR_MESSAGE()asErrorMessage;
ENDCATCH;
例12-29创建用户定义消息
sp_addmessage5001,16,'messagetest','us_english','ture'
例12-30调用定义的错误消息
raiserror(5001,16,1)
例12-31创建用于保护数据库中的数据表不被修改和删除的存储过程
CREATETRIGGER禁止对数据表操作
ONDATABASE
FORDROP_TABLE,ALTER_TABLE
AS
PRINT'对不起,您不能对数据表进行操作'
ROLLBACK;
例12-32创建用于保护当前SQLServer服务器里所有数据库不能被删除的存储过程
CREATETRIGGER不允许删除数据库
ONallserver
FORDROP_DATABASE
AS
PRINT'对不起,您不能删除数据库'
ROLLBACK;
GO
示例12-33:
(1)建立一个用于记录数据库修改状态的表:
CREATETABLE日志记录表(
编号intIDENTITY(1,1)NOTNULL,
事件varchar(5000)NULL,
所用语句varchar(5000)NULL,
操作者varchar(50)NULL,
发生时间datetimeNULL,
CONSTRAINTPK_日志记录表PRIMARYKEYCLUSTERED
(
编号ASC
)WITH(IGNORE_DUP_KEY=OFF)ON[PRIMARY]
)ON[PRIMARY]
GO
(2)建立DDL触发器:
例12-33创建用来记录数据库修改状态的触发器
CREATETRIGGER记录日志
ONDATABASE
FORDDL_DATABASE_LEVEL_EVENTS
AS
DECLARE@logXML
SET@log=EVENTDATA()
INSERT 日志记录表
(事件,所用语句,操作者,发生时间)
VALUES
(
@log.value('(/EVENT_INSTANCE/EventType)[1]','nvarchar(100)'),
@log.value('(/EVENT_INSTANCE/TSQLCommand)[1]','nvarchar(2000)'),
CONVERT(nvarchar(100),CURRENT_USER),
GETDATE()
)
GO
例12-34判断库存数量是否为0
If(Select库存数量FromDeleted)>0
Begin
Print‘库存数量大于零时不能删除此记录’
RollbackTransaction
End
例12-35
sqlCommandsqlComm=SqlContext.GetCommand()
sqlComm.CommandText="SELECTcolumn_1frominserted";
例12-36
C#
updatedColumnCount=SqlContext.TriggerContext.ColumnCount;
updatedColumns=newbool[updatedColumnCount];
for(inti=0;i updatedColumns[i]=SqlContext.TriggerContext.IsUpdatedColumn(i); counter=0; foreach(booltinupdatedColumns) SqlContext.Pipe.Send("Updatedcolumn"+reader.GetName(counter++)+"? "+t.ToString()); VisualBasic updatedColumnCount=SqlContext.TriggerContext.ColumnCount ReDimupdatedColumns(updatedColumnCount) DimiAsInteger Fori=0ToupdatedColumnCount-1 updatedColumns(i)=SqlContext.TriggerContext.IsUpdatedColumn(i) counter=0 DimtAsBoolean ForEachtInupdatedColumns SqlContext.Pipe.Send("Updatedcolumn"_ &reader.GetName(counter)&"? "&t.ToString()) counter+=1 Next Next 第一步: 在VisualStudio2005中编写代码 usingSystem; usingSystem.Data; usingSystem.Data.Sql; usingSystem.Data.SqlServer; usingSystem.Data.SqlTypes; publicpartialclassTriggers { //Enterexistingtableorviewforthetargetanduncommenttheattributeline //[SqlTrigger(Name="myTrigger",Target="Table1",Event="FORUPDATE")] publicstaticvoidmyTrigger() { SqlTriggerContextmyTriggerContext=SqlContext.GetTriggerContext(); SqlPipemyPipe=SqlContext.GetPipe(); SqlCommandmyCommand=SqlContext.GetCommand(); if(myTriggerContext.TriggerAction==TriggerAction.Insert) { myCommand.CommandText="select*fromspTestTbl"; } myPipe.Execute(myCommand); } } 第三步: 注册 示例: CREATETRIGGERmyTriggerONtestTbl1FORINSERT ASEXTERNALNAMEudt.Triggers.myTrigger 注册到SQLServer2008 示例: CREATETRIGGERmyTriggerONtestTb11FORINSERT AS EXTERNALNAMEudt.Triggers.myTrigger 操作代码如下(文件名为: staffNoQuery.jsp): <%@pagecontentType="text/html;charset=gb2312"language="java"import="java.sql.*"errorPage=""%> DOCTYPEHTMLPUBLIC"-//W3C//DTDHTML4.01Transitional//EN""http: //www.w3.org/TR/html4/loose.dtd">
--
.style1{font-size:
24px}
-->
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 触发器