sql: TRIGGER

  • 2020-04-17 06:22
  • 新浦京计算机网络
  • Views
--Common Table Expressions(CTE)WITH HighSample (SampleId,SampleTitle,SampleContent) AS(  SELECT SampleId,SampleTitle,SampleContent From CompetitionSample  WHERE SampleId>1)SELECT * From HighSample--游标DECLARE curSample INSENSITIVE CURSORFOR SELECT TOP 10 SampleId,SampleTitle,SampleContent FROM CompetitionSampleDECLARE @SampleId intDECLARE @SampleTitle nvarchar(540)Declare @SampleContent nvarchar(1000)Open curSampleFETCH NEXT FROM curSample INTO @SampleId,@SampleTitle,@SampleContentWHILE @@Fetch_Status=0 BEGIN  PRINT @SampleTitle+','+@SampleContent  Fetch NEXT FROM curSample Into @SampleId,@SampleTitle,@SampleContent ENDCLOSE curSampleDEALLOCATE curSample--N到M条记录(要有主索引ID)--Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID  Descselect * from ShoppingCouponsListSelect Top (15-10) * From ShoppingCouponsList Where ShoppingCouponsID in (Select Top 15 ShoppingCouponsID From ShoppingCouponsList) Order by ShoppingCouponsID  Desc--触发器(添加,修改)--text,ntext 无法创建约束,可以用此方法drop TRIGGER Sample_insertCREATE TRIGGER DuSample_insertON CompetitionSampleFOR INSERT,UPDATE AS --常见的触发器有三种:分别应用于Insert , Update , Delete 事件declare @s nvarchar(500)set @s='err,geovindu'IF EXISTS (SELECT * FROM CompetitionSample where charindex('</title>',SampleContent)=1)beginROLLBACK TRANSACTIONRAISERROR('有非法字符',16,1,@s)end--修改不了update CompetitionSample set SampleContent='</title>' where SampleId='1'--添加不了insert into CompetitionSample(SampleTitle,SampleBrandId,SamplePlatformId,SampleSmallPic,SampleContent,SampleAuthor,SampleIs) values('d2',1,1,'gif','</title>2','g2',0)select * from CompetitionSample where charindex('</title>',SampleContent)=1

 

上一篇:DISTINCT和GROUP BY的区别 下一篇:没有了