sql: TRIGGER

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54

--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 CURSOR FOR SELECT TOP 10 SampleId,SampleTitle,SampleContent FROM CompetitionSample DECLARE @SampleId int DECLARE @SampleTitle nvarchar(540) Declare @SampleContent nvarchar(1000) Open curSample FETCH NEXT FROM curSample INTO @SampleId,@SampleTitle,@SampleContent WHILE @@Fetch_Status=0   BEGIN     PRINT @SampleTitle+','+@SampleContent     Fetch NEXT FROM curSample Into @SampleId,@SampleTitle,@SampleContent   END CLOSE curSample DEALLOCATE curSample   --N到M条记录(要有主索引ID) --Select Top M-N * From 表 Where ID in (Select Top M ID From 表) Order by ID   Desc select * from ShoppingCouponsList Select Top (15-10) * From ShoppingCouponsList Where ShoppingCouponsID in (Select Top 15 ShoppingCouponsID From ShoppingCouponsList) Order by ShoppingCouponsID   Desc     --触发器(添加，修改） --text,ntext 无法创建约束，可以用此方法 drop TRIGGER Sample_insert   CREATE TRIGGER DuSample_insert ON CompetitionSample FOR INSERT,UPDATE AS --常见的触发器有三种：分别应用于Insert , Update , Delete 事件 declare @s nvarchar(500) set @s='err,geovindu' IF EXISTS (SELECT * FROM  CompetitionSample  where charindex('</title>',SampleContent)=1) begin ROLLBACK TRANSACTION RAISERROR('有非法字符',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